strSQL with multiple criteria problem

I have a string with multiple and cannot get it to work.  It will work with any two of the criteria but not the third.  I have tried so many different ways.  What am I missing?

Dim sLoadLoc as String
Dim sLoadNum as Variant
Dim sLoadLine as Variant

    sLoadNum = Forms![frm_bol_hed]![txt_loadNum]
    sLoadLoc = Forms![frm_bol_hed]![txt_loc]
    sLineNum = Me.LD_DET_LINE

            strSQL = "select * from TBL_LOAD_DET where [LD_DET_LOC] = '" & sLoadLoc & "' And [LD_DET_LINE] =" & sLineNum And [LD_DET_NUM] = " & sLoadNum"
        Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
LVL 2
pskeensAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
"strLoadNUm and strLineNum are numeric types.  "
Then it would help if the naming convention was not implying String ... So ...


            strSQL = "select * from TBL_LOAD_DET where [LD_DET_LOC] = " & Chr(34) & sLoadLoc &  Chr(34) & " And [LD_DET_LINE] =" &  sLineNum  & " And [LD_DET_NUM] = " & sLoadNum  
        Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

Try this:



            strSQL = "select * from TBL_LOAD_DET where [LD_DET_LOC] = " & Chr(34) & sLoadLoc &  Chr(34) & " And [LD_DET_LINE] =" & Chr(34) & sLineNum & Chr(34) & " And [LD_DET_NUM] = " & Chr(34) & sLoadNum & Chr(34)
        Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
btw ... Chr(34) is a Double Quote

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
pskeensAuthor Commented:
Negative

"Datatype mismatch in expression"

Same error I was getting before.
0
 
pskeensAuthor Commented:
strLoadNUm and strLineNum are numeric types.  These should not be in double quotes, correct?
0
 
mbizupCommented:
You should add the line "Option Explicit" to the top of any code module, right under Option Compare Database.  Doing so forces you to explicitly declare any variables used.

What data type is sLineNum?  numeric or text?  You don't have a Dim statement for it.

When writing SQL statements, you need to delimit text fields with quotes, dates with # marks, and numeric fields do not get delimited.

mx's code above assumes that all fields are text.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
In fact, you have

Dim sLoadLoc as String
Dim sLoadNum as Variant
Dim sLoadLine as Variant

Then


    sLoadNum = Forms![frm_bol_hed]![txt_loadNum]
    sLoadLoc = Forms![frm_bol_hed]![txt_loc]
    sLineNum = Me.LD_DET_LINE

So ... because of the 's' prefix ... seems to imply String.

mx
0
 
pskeensAuthor Commented:
Winner winner, chicken dinner!  You the man!  I see what I missed now.  Stupid ampersands!!!  haha.

Thanks
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Can we make that fish, as I am a Fishetarian.

And how about we work on those naming conventions :-)

mx
0
 
pskeensAuthor Commented:
naming conventions?  Which ones?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The ones that have a prefix of 's' (which would imply String) that are actually numeric ...

mx
0
 
pskeensAuthor Commented:
Oh yeah, that may be a good thing.  I will change after this application is done. lol.  I don't want to go back and change all the code in this thing.  Maybe after its complete then I will go back and clean up.
0
All Courses

From novice to tech pro — start learning today.