Type Mismatch Error

JustPunkin · Jan 8, 2009

OK, bear with me. I am a total novice at this stuff (access, SQL, vb). I am working on creating a form that will allow me to query our project database based on several varying criteria. I get the idea of creating a query on the fly, and I can get this to work for the data we have that is in combo boxes. What I can't get to work is the data that we have in check boxes (yes/no data type).

This is the start of my routine

Code:

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryTest1")
strSQL = "SELECT [Mechanical Features].* " & _
         "FROM [Mechanical Features] " & _
         "WHERE [Mechanical Features].[Fastener Type]='" & Me.cboFastType.Value & "'" & _
         "AND [Mechanical Features].[MW Design]='" & Me.cboMWDesign.Value & "'" & _
         "AND [Mechanical Features].[Tile Deck]='" & Me.chkTileDeck.Value & "'" & _
         "AND [Mechanical Features].[Foldover weirs]='" & Me.chkFOWeir.Value & "'" & _
         "ORDER BY [Mechanical features].[Fastener Type];"
         
Debug.Print strSQL
      
End Sub

When I pop the SQL string into a query to test it, I get a data type mismatch in criteria expression error. I've narrowed it down to the check boxes (which are the tile deck and foldover weirs fields).

When I check the SQL string after running it with criteria, I get this:

Code:

SELECT [Mechanical Features].*
FROM [Mechanical Features]
WHERE [Mechanical Features].[Fastener Type]='UNC' And [Mechanical Features].[MW Design]='Oval' And [Mechanical Features].[Tile Deck]='-1' And [Mechanical Features].[Foldover Weirs]='0'
ORDER BY [Mechanical features].[Fastener Type];

which looks to be correct to me (on the form I had selected yes for Tile Deck and no for Foldovers). But it won't let me run the query.

Any ideas what I'm doing wrong? Remember, I'm a total noob when it comes to this stuff, so be gentle!

rainman89 · Jan 8, 2009

have you tried using true and false? or yes/no instead of '-1' and '0' ?

namliam · Jan 8, 2009

JustPunkin said:
I get the idea

Get this idea...
Dont use spaces or special characters in ANY name(s) anywhere.
Do use a naming convention, all tables should start with tbl or something simular and queries with qry etc.
You do this more or less using chk for check boxes so you know this, but apply it constantly throughout your database!

Yes/No fields are actually number fields (i.e. -1 and 0 for Yes and No respectively)
Text fields are enclosed by quotes like you are doing
Date fields are enclosed by hashes (#)
Number fields are not enclosed at all...

Good luck !

rainman89 · Jan 8, 2009

namliam said:
Yes/No fields are actually number fields (i.e. -1 and 0 for Yes and No respectively)

my next suggestion!

JustPunkin · Jan 8, 2009

namliam said:
Get this idea...
Dont use spaces or special characters in ANY name(s) anywhere.
Do use a naming convention, all tables should start with tbl or something simular and queries with qry etc.
You do this more or less using chk for check boxes so you know this, but apply it constantly throughout your database!

Yes/No fields are actually number fields (i.e. -1 and 0 for Yes and No respectively)
Text fields are enclosed by quotes like you are doing
Date fields are enclosed by hashes (#)
Number fields are not enclosed at all...

Good luck !

I understand the naming convention thing. However, this database was started a long time ago, and I am just now coming in to it. I'm afraid to try and start renaming things now, because I'm not sure what else it would affect. Anything that I have personally worked on is named that way. You'll notice the only thing not named that way is the table and its associated fields.

I also understand that the yes/no fields are -1 and 0; that's what I have in my program.

I also understand that is is giving me an error, and that's what I'm trying to solve.

Thanks for the help.

allan57 · Jan 8, 2009

Dont use the apostrophe surrounding the criteria when it is a numeric value.

Examples

For numeric values: DLookup("FieldName" , "TableName" , "Criteria = -1")

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #21/04/57#")

edit
See you beat me this time Namliam

edit

namliam · Jan 8, 2009

Also to keep your SQL readable, try inserting vbNewLine

strSQL = "SELECT [Mechanical Features].* " & vbnewline & _

Then again... the Line continuation is only allowing so many lines... I suggest you start addapting to:
strSQL = ""
strSQL = strSQL & " Select .. " & vbnewline
strSQL = strSQL & " From .. " & vbnewline
etc.

1) its more constant
2) More reliant without running into problems

Lastly...
make the last step and format your sql...

Code:

strSQL = "SELECT [Mechanical Features].* " & _
         "FROM   [Mechanical Features] " & _
         "WHERE  [Mechanical Features].[Fastener Type]='" & Me.cboFastType.Value & "'" & _
         "  AND  [Mechanical Features].[MW Design]    ='" & Me.cboMWDesign.Value & "'" & _

To make it yet more readable....

JustPunkin · Jan 8, 2009

rainman89 said:
my next suggestion!

I thought about that, and if I manually go into my query and select the criteria for those two fields and true and false, the query runs.

I guess I don't know how to automate that.

This is extremely new to me, and WAY over my head, but I'm plugging through anyway.

Is there a way to make the SQL statement replace the -1 and 0 it automatically assigns to true or false?

JustPunkin · Jan 8, 2009

allan57 said:
Dont use the apostrophe surrounding the criteria when it is a numeric value.

Examples

For numeric values: DLookup("FieldName" , "TableName" , "Criteria = -1")

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #21/04/57#")

edit
See you beat me this time Namliam
edit

OK, so does that mean I have to change this:

Code:

"AND [Mechanical Features].[Tile Deck]='" & Me.chkTileDeck.Value & "'" & _

to

Code:

"AND [Mechanical Features].[Tile Deck]=" & Me.chkTileDeck.Value & "" & _

( I took out the single quotes)

Oh, and thanks for the quick responses. I appreciate the help!!!

namliam · Jan 8, 2009

Please re-read my post:

namliam said:
Text fields are enclosed by quotes like you are doing
Date fields are enclosed by hashes (#)
Number fields are not enclosed at all...

Or Alan's

allan57 said:
Dont use the apostrophe surrounding the criteria when it is a numeric value.

Examples

For numeric values: DLookup("FieldName" , "TableName" , "Criteria = -1")

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #21/04/57#")

edit
See you beat me this time Namliam
edit

and also my new post:
http://www.access-programmers.co.uk/forums/showpost.php?p=792911&postcount=7

Whoops edit:
I see you did read Alan's post after all...

Just make sure you also caught my previous one

Yes... you do it the way you posted, take out the quotes

JustPunkin · Jan 8, 2009

namliam said:
Please re-read my post:

Or Alan's

and also my new post:
http://www.access-programmers.co.uk/forums/showpost.php?p=792911&postcount=7

Whoops edit:
I see you did read Alan's post after all...

Just make sure you also caught my previous one

Yes... you do it the way you posted, take out the quotes

Thanks - I'm off to try that. Don't be surprised to see me back here again, however.

You guys rock - the quick and helpful answers were WAY more than I expected.

JustPunkin · Jan 8, 2009

Just to be sure I'm doing this right, text fields are enclosed in quotes (they can be single or double). It doesn't matter that the text field will contain a number as the text, it is still enclosed in quotes?

Thanks in advance!!

allan57 · Jan 9, 2009

A text field can hold a number, and requires the use of apostrophes.

if you use a text box/variable from your form/code then Text fields are enclosed using apostrophes and double quotes ie

DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")

for a fixed value

DLookup("FieldName" , "TableName" , "Criteria= 'string'")

namliam · Jan 9, 2009

The content of a field is unimportant, the TYPE of field is....

Using ' vs " is important as your data contained within the (double) quotes cannot hold the (double) quote that is surrounded by...
i.e. the name 'D'Angelo' will cause an error, while "D'Angelo" will not

Type Mismatch Error

JustPunkin

Registered User.

rainman89

I cant find the any key..

namliam

The Mailman - AWF VIP

rainman89

I cant find the any key..

JustPunkin

Registered User.

allan57

Allan

namliam

The Mailman - AWF VIP

JustPunkin

Registered User.

JustPunkin

Registered User.

namliam

The Mailman - AWF VIP

JustPunkin

Registered User.

JustPunkin

Registered User.

allan57

Allan

namliam

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread