Data type mismatch in criteria

  • Thread starter Thread starter Database Girl
  • Start date Start date
D

Database Girl

I get a error message on the following criteria string:

strCriteria = "[SAPNumber] = '" & Me!txtPO & "' AND [LineNo] = ' " &
Me!txtLine & "' AND [SunTicket] = '0'"

I know it is the LineNo that is causing the issue because when I remove it
the code works. The field on the form is a unbound text box. The properties
on the text box is set to "General Number" for format and decimal places are
set to "Auto". The "LineNo" field on the table is a primary key and is
formated with a data type of number, a field size of "Byte" and decimal
places of "Auto".

I just cannot figure out why I am getting the data type mismatch.

Your assistance would be appreciated.
 
Bytes are numeric fields, not text.

Remove the single quotes on either side of Me!txtLine
 
The SQL is executed against the table. Since the table defines lineNo as a
byte, the sql has to treat it as a number. Basically, remove the single
quotes around the line number value:

strCriteria = "[SAPNumber] = '" & Me!txtPO & "' AND [LineNo] = " &
Me!txtLine.Value & " AND [SunTicket] = '0'"

Access automatically converts the string value of the textbox to a number,
or you can make an explicit conversion:

strCriteria = "[SAPNumber] = '" & Me!txtPO & "' AND [LineNo] = " &
CByte(Me!txtLine.Value) & " AND [SunTicket] = '0'"
 
Opps. You are right. I works like a charm now. Thanks for you help.

Douglas J. Steele said:
Bytes are numeric fields, not text.

Remove the single quotes on either side of Me!txtLine

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Database Girl said:
I get a error message on the following criteria string:

strCriteria = "[SAPNumber] = '" & Me!txtPO & "' AND [LineNo] = ' " &
Me!txtLine & "' AND [SunTicket] = '0'"

I know it is the LineNo that is causing the issue because when I remove it
the code works. The field on the form is a unbound text box. The
properties
on the text box is set to "General Number" for format and decimal places
are
set to "Auto". The "LineNo" field on the table is a primary key and is
formated with a data type of number, a field size of "Byte" and decimal
places of "Auto".

I just cannot figure out why I am getting the data type mismatch.

Your assistance would be appreciated.
Click to expand...
Click to expand...
 
Back
Top