Runtime error 3464 data type mismatch in criteria expression (1 Viewer)

[fearmichaele]

i have read other post about this error and tried adapting their fixes to fix my error without success.

i have a form which the user enters a number in a text box in order to search for that number in a query. if the number is not found in the query I want a message box to pop up saying "number not found, check number and try again" and not open a new form.

everything i tried even if a number is entered and not located the next form opens anyway blank. i dont want the form to open if the number is not in the query. Simply, i am checking the query first to see if the number is present and if not do not open the next form, but if it is open the next form showing the information for the number entered.

i have searched other forums including this one and have not found a solution. the current code i adapted was from another forum. now when i get to the highlighted line i get run time error 3464. i cant figure out where the error is pointing to regarding data type mismatch.

the EquipmentPK is the primary key field from the table which show in the query - Tag is the field in the query that holds the number the user is searching for. me.text0 is the text box the user enters the number they want to look for. qry_Equipment_all is the name of the union query I am using.

note not all equipment have a tag number so that field on the original table is set to short text and there are null values. so i was trying to count the number of equipmentPK where the Tag field matches the me.text0 entered by the user. if the number does not exist in the query then i do not want the form "Frmqry_EquipmentAll" to open at all. if it does exist then open the form.

the code is as follows

Private Sub Command4_Click()
On Err GoTo Errhand

Dim tagtemp As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim intHolder As Integer

stLinkCriteria = "[Tag] =" & Me.Text0
stDocName = "qryfrm_EquipmentAll"

intHolder = DCount("EquipmentPK", "qry_Equipment_all", stLinkCriteria)
If intHolder > 0 Then
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, , acDialog
Else
MsgBox "Tag number not located, please check and reenter", vbOKOnly
End If
Exit_Command4_Click:
Exit Sub

Errhand:
MsgBox "Error number = " & Err.Number & " and Description is " & Err.Description, vbOKOnly
GoTo Finish

Finish:
End Sub

i am now crosseyed and i am at a loss. what am i missing. any assistance is greatly apprectiated.

 

[theDBguy]

Hi. When you say [Tag] is a number, is it a Number data type in the table design?

 

[MajP]

my guess it is a number stored as text
"[Tag] ='" & Me.Text0 & "'"

 

[fearmichaele]

Hi. When you say [Tag] is a number, is it a Number data type in the table design?

the table design, TAG is a short text.

 

[fearmichaele]

my guess it is a number stored as text
"[Tag] ='" & Me.Text0 & "'"

yes it is a number stored as text. i did add the ampersand and the quotes at the end like you typed and still throws the same error.

 

[fearmichaele]

my guess it is a number stored as text
"[Tag] ='" & Me.Text0 & "'"

wait!!

MajP that was it. i didn't see the single quote you placed at first. this did the trick and i thank you very much.!!!!!

i figured it was something that simple.

it works like a charm.

Thanks again.

 

[isladogs]

Can I suggest a simpler solution that would give a better user experience.
Instead of using a text box and hoping the user guesses correctly, have a combo box or listbox with a row source listing all the available values in your data.
If you do this, users can only enter a valid number and the rest of your code can be scrapped.

Or am I missing something obvious?

 

[theDBguy]

wait!!

MajP that was it. i didn't see the single quote you placed at first. this did the trick and i thank you very much.!!!!!

i figured it was something that simple.

it works like a charm.

Thanks again.

Hi. Glad to hear you got it sorted out. Is there a particular reason why a number is stored as text? Just curious...

 

[fearmichaele]

Can I suggest a simpler solution that would give a better user experience.
Instead of using a text box and hoping the user guesses correctly, have a combo box or listbox with a row source listing all the available values in your data.
If you do this, users can only enter a valid number and the rest of your code can be scrapped.

Or am I missing something obvious?

ilsadog

well I did consider that option, but the user may only have a tag number to look up. if the tag number is in the database, then the user can pull up the equipment information such as where its located, who it is assigned to, purchase order number and cost for the equipment, etc. if that tag number is not in the database then I needed a pop up that said not there. Some of our equipment is not tagged by our procurement services department if under a cost threshold. so the equipment is stored by location, or employee assigned to, stationary or portable. If the user is trying to locate a certain equipment and its not in the database then they need to report it to management to try and locate the equipment and have the database managers enter the info.

does that make sense?

 

[fearmichaele]

Hi. Glad to hear you got it sorted out. Is there a particular reason why a number is stored as text? Just curious...

well I questioned that as well when i had taken over this project. the answer I was given is currently our institution uses numbers to tag equipment but are exploring using alphanumeric to tagging so I left it alone.

 

[isladogs]

In my opinion, not really.
Normally a combo would be limited to values in the list but you can also set Limit to List = No.
If you did so, a message box could be displayed when a user enters an invalid number....if that's what you really want.

Personally I hate having to guess what data is valid.