VB question

[Stang02GT]

Yes thats what i was referring too, because some feilds have an X5 and not a Z6
(one is a tracking number and the other is a request number)

So id like it to try and get it to work if there is data in either feild apposed to just one





Thanks again!

[NeoPa]

Strangely enough I posted this explanation in a thread earlier today. It's for that question (Need to create a selective(filte red) lookup report (thingy?) Edit Post) obviously, but I think the concept could be helpful here.

A problem many people struggle with when applying filter strings is "How to apply the AND and OR keywords?"
Seems obvious, but if I said we wanted to see records that were from January AND February, but no other months, would you use AND or OR in the Filter string?
If you said AND then you'd be wrong.
The Filter is checking each record as it is processed. Can any record be from both January AND February? No, it can't (You'd have no results at all). For each record, you want to determine if it is either January OR February (even though you want to include records from January AND February in your results set).

[Stang02GT]

I've got the code working to the point where if i remove the OR clause it works fine but when i add the OR clause i get this error


Run-time error '3464'

Data type mismatch in criteria expression

[JKing]

Post the SQL string with the OR statement in it that you are using. Is the field you are adding text? date? number? Text fields should be enclosed by ' and dates should be enclosed by #.

[Stang02GT]

X5 and Z6 are both number fields

Private Sub Command24_Click()
Dim strSQL As String
    
    strSQL = "INSERT INTO SRMisc ( [Tracker Item], Description, " & _
           "Comments, Requestor, [SR Num] ) " & _
           "SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6 " & _
           "FROM Tracker WHERE Tracker.Z6='" & Me.Z6 & "' OR Tracker.X5='" & Me.X5 & "';"
    DoCmd.RunSQL strSQL
    End Sub

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [CODE] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

MODERATOR.

PS. I have had to change your posts twice already in this thread alone (See post #7).

[JKing]

Try changing this line:

[code=vb]
"FROM Tracker WHERE Tracker.Z6= " & Me.Z6 & " OR Tracker.X5=" & Me.X5 & ";"
[/code]

All I did was remove the single quotes in the statement. Since they are number fields they don't require the single quotes and it's probably assuming you're trying to pass text data into a number field causing your error.

[Stang02GT]

Try changing this line:

[code=vb]
"FROM Tracker WHERE Tracker.Z6= " & Me.Z6 & " OR Tracker.X5=" & Me.X5 & ";"
[/code]

All I did was remove the single quotes in the statement. Since they are number fields they don't require the single quotes and it's probably assuming you're trying to pass text data into a number field causing your error.

Its still giving me the same error.

[NeoPa]

Its still giving me the same error.

The format of your SQL was fundamentally correct (both before and after the change suggested). What we don't really know (unless I missed something somewhere) is the type of data stored in the two fields Z6 & X5. Actually it's the field type we need to be precise. There are three fundamental types handled by SQL :-

1. String (Text)
2. Numeric
3. Date (/Time)

We need to know which type these fields are in order to formulate the WHERE clause correctly.

[Stang02GT]

The format of your SQL was fundamentally correct (both before and after the change suggested). What we don't really know (unless I missed something somewhere) is the type of data stored in the two fields Z6 & X5. Actually it's the field type we need to be precise. There are three fundamental types handled by SQL :-

1. String (Text)
2. Numeric
3. Date (/Time)

We need to know which type these fields are in order to formulate the WHERE clause correctly.

They are both numeric


P.S. really sorry about the problems posting my code :(

[NeoPa]

In that case JKing's recommendation is fundamentally correct.
You needn't worry about the CODE warning. You've been quite helpful around the place and that gives you plenty of leeway in my book.

We need to revisit posts #19 & #20 then.
To get the actual SQL string (as requested) add this line after your line #7 in post #20.
When you run the code you should find the actual SQL string used in the immediate pane of the VBA window.
I suspect you'll find one of the values not set, but let's wait for the SQL before jumping the gun.

[NeoPa]

X5 and Z6 are both number fields (Post #20)

My turn to apologise :( You'd already answered my question.

[Stang02GT]

My turn to apologise :( You'd already answered my question.

lol its quite alright


Concerning the issue with my code, if i remove the OR clause the code works fine with no errors, but as soon as i place it back into my code it shots that error at me

[JKing]

Seems you did end up jumping the gun there NeoPa :P. I do my best to get all info I can before trying to solve the error. I am however going to have to agree that there is likely a null value being passed in. If you are allowing the user to only type into one textbox and leave the other blank(null) this will result in a null being evaluated in the where criteria.

Solution for this?

[code=vb]
Private Sub Command24_Click ()
Dim strSQL As String
Dim blnRunSQL As Boolean

blnRunSQL = false

strSQL = "INSERT INTO SRMisc ( [Tracker Item], Description, " & _
"Comments, Requestor, [SR Num] ) " & _
"SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6 " & _
"FROM Tracker "
If Not IsNull(Me.z6) AND IsNull(Me.x5) Then
strSQL = strSQL & "WHERE Tracker.Z6 = " & Me.Z6
blnRunSQL = true
Elseif Not IsNull(Me.X5) And IsNull (Me.z6) Then
strSQL = strSQL & "WHERE Tracker.X5 = " & Me.X5
blnRunSQL = true
Elseif Not IsNull(Me.X5) And Not IsNull(Me.Z6) Then
strSQL = strSQL & "WHERE Tracker.Z6= " & Me.Z6 & " OR Tracker.X5= " & Me.X5 & ";"
blnRunSQL = true
Elseif IsNull(Me.X5) And IsNull(Me.z6) Then
MsgBox "You must enter a value to search by."
End If

If blnRunSQL Then
DoCmd.RunSQL strSQL
End If
End Sub
[/code]

This will check to see which textbox they've typed into or check both. If both boxes are left blank prompt the user to enter something. The runSQL is only executed when something has been filled in.

[NeoPa]

Stang, You need to visit post #26 and provide the information requested.