A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access 2003



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2010, 09:35 PM posted to microsoft.public.access.queries
S.E.
external usenet poster
 
Posts: 1
Default Access 2003

I get a "type mismatch in expression" when saving a query. Why do I get this
and how do I correct this? My suspicion is my customerID and barcode fields
are not both autonumbers or the same type to relate but under the
relationships they join up. Please help.
  #2  
Old May 21st, 2010, 10:55 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Access 2003

S.E. wrote:

I get a "type mismatch in expression" when saving a query. Why do I get this
and how do I correct this? My suspicion is my customerID and barcode fields
are not both autonumbers or the same type to relate but under the
relationships they join up.


What are the types of the fields in the table and what does
the query's SQL view look like?

If one is Text and the other is a number type, then use a
convesion function (CStr, CLng or ??).

--
Marsh
MVP [MS Access]
  #3  
Old May 22nd, 2010, 03:33 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access 2003

On Fri, 21 May 2010 13:35:01 -0700, S.E.
wrote:

I get a "type mismatch in expression" when saving a query. Why do I get this
and how do I correct this? My suspicion is my customerID and barcode fields
are not both autonumbers or the same type to relate but under the
relationships they join up. Please help.


My guess is that you're another victim of the misleading "Lookup Wizard" field
type. A barcode would normally be a Text field, and I'm guessing that the
CustomerID is an Autonumber (a long integer).

Posting the SQL of the query and your fields' datatypes would help.
--

John W. Vinson [MVP]
  #4  
Old May 22nd, 2010, 02:01 PM posted to microsoft.public.access.queries
S.E.[_2_]
external usenet poster
 
Posts: 4
Default Access 2003



"Marshall Barton" wrote:

S.E. wrote:

I get a "type mismatch in expression" when saving a query. Why do I get this
and how do I correct this? My suspicion is my customerID and barcode fields
are not both autonumbers or the same type to relate but under the
relationships they join up.


What are the types of the fields in the table and what does
the query's SQL view look like?

If one is Text and the other is a number type, then use a
convesion function (CStr, CLng or ??).

--
Marsh
MVP [MS Access]
.
Thanks for a response. One is a autonumber that is a long integer. The other is a text which I input using a barcode scanner. The barcode happens to be setup to input the customer's ID which corresponds to the customer ID in the other table. I'm not sure what you mean by SQL view b/c I have limited knowledge on this aspect. BTW, the tables are not joined in any way through the relationship if that matters.


How do you setup a conversion function?
  #5  
Old May 22nd, 2010, 02:05 PM posted to microsoft.public.access.queries
S.E.[_2_]
external usenet poster
 
Posts: 4
Default Access 2003



"John W. Vinson" wrote:

On Fri, 21 May 2010 13:35:01 -0700, S.E.
wrote:

I get a "type mismatch in expression" when saving a query. Why do I get this
and how do I correct this? My suspicion is my customerID and barcode fields
are not both autonumbers or the same type to relate but under the
relationships they join up. Please help.


My guess is that you're another victim of the misleading "Lookup Wizard" field
type. A barcode would normally be a Text field, and I'm guessing that the
CustomerID is an Autonumber (a long integer).

Posting the SQL of the query and your fields' datatypes would help.
--

John W. Vinson [MVP]
.
Thanks for a response. Correct on all accounts. One is a autonumber that is a long integer. The other is a text which I input using a barcode scanner. The barcode happens to be setup to input the customer's ID which corresponds to the customer ID in the other table. I'm not sure about the SQL's b/c I have limited knowledge on this aspect. BTW, the tables are not joined in any way through the relationship if that matters.


Can you walk me through posting the SQL you referred to above?
  #6  
Old May 22nd, 2010, 03:29 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access 2003

On Sat, 22 May 2010 06:05:01 -0700, S.E. wrote:


Can you walk me through posting the SQL you referred to above?


Open the query in design view. Select View... SQL from the menu, or use the
dropdown box at the left of the toolbar - it has three choices, Design,
Datasheet and SQL. Copy and paste the SQL text to a message here.

I won't be around for a few days but there will be someone who can help.
--

John W. Vinson [MVP]
  #7  
Old May 27th, 2010, 09:06 PM posted to microsoft.public.access.queries
S.E.[_2_]
external usenet poster
 
Posts: 4
Default Access 2003



"S.E." wrote:



"Marshall Barton" wrote:

S.E. wrote:

I get a "type mismatch in expression" when saving a query. Why do I get this
and how do I correct this? My suspicion is my customerID and barcode fields
are not both autonumbers or the same type to relate but under the
relationships they join up.


What are the types of the fields in the table and what does
the query's SQL view look like?

If one is Text and the other is a number type, then use a
convesion function (CStr, CLng or ??).

--
Marsh
MVP [MS Access]
.
Thanks for a response. One is a autonumber that is a long integer. The other is a text which I input using a barcode scanner. The barcode happens to be setup to input the customer's ID which corresponds to the customer ID in the other table. I'm not sure what you mean by SQL view b/c I have limited knowledge on this aspect. BTW, the tables are not joined in any way through the relationship if that matters.


How do you setup a conversion function?


Still looking for help and answers to above.
  #8  
Old May 27th, 2010, 11:01 PM posted to microsoft.public.access.queries
S.E.[_2_]
external usenet poster
 
Posts: 4
Default Access 2003 + a few other questions



"John W. Vinson" wrote:

On Sat, 22 May 2010 06:05:01 -0700, S.E. wrote:


Can you walk me through posting the SQL you referred to above?


Open the query in design view. Select View... SQL from the menu, or use the
dropdown box at the left of the toolbar - it has three choices, Design,
Datasheet and SQL. Copy and paste the SQL text to a message here.

I won't be around for a few days but there will be someone who can help.
--

John W. Vinson [MVP]
.
Thanks. I've been out too. I'll give this a shot and get back to you. In the meantime, I've got another question or two regarding calculations.


First, I want to take today's date and subtract 12 years from it. Is there a
expression that I can use in a form to show my staff what they need?

Secondly, I have another report where I have a qty text field that gives a
number to each result. These are grouped by a certain category which has an
assigned $ value to it. Is there a way to tally the total number of entries
with the different amounts of each category? This may be too confusing to
understand but I gave it my best in explaining it without having you see it
to understand it.

Thanks for any additional help.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.