Need another Pair of eyes - type mismatch error

[Indigo]

If someone could please be another set of eyes for me? I can't see the error in my statement below:

            Dim HoldDate As Date
            Dim HoldCC As String
 
            HoldDate = Forms!frmSecurity!ShiftDate
            HoldCC = Forms!frmSecurity!EMPCC
 
strSQL2 = "SELECT NEmailNotes " & _
                      " WHERE NEmailNotes.ShiftDate =#" & HoldDate & "#" And " NEmailNotes.CC='" & HoldCC & "'"

When I attempt to tweak and test things with this, i.e. try the following:

strSQL2 = "SELECT NEmailNotes " & _
                      " WHERE NEmailNotes.CC='" & HoldCC & "'"

I get a Run time error 3075 - missing operator in query expression.


I can't see the forest for the trees here, so if another pair of eyes could take a look and let me know what I messed up, it would be appreciated. Thank you!

 

[pr2-eugin]

You seem to be missing the FROM clause. Then improper use of concatenation.

 

[spikepl]

You need to have a better SQL-creation technique and to use the built-in syntax checker. Follow this recipe: http://www.baldyweb.com/ImmediateWindow.htm

 

[Indigo]

Thank you both....face palm about the FROM clause....but....

So if I try to run my second example utilizing the syntax checker the immediate window displays:

SELECT FROM NEmailNotes WHERE NEmailNotes.CC='ABC13'


but I now get a runtime error 3141 - The SELECT statement included a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

 

[gemma-the-husky]

SELECT * FROM NEmailNotes WHERE NEmailNotes.CC='ABC13'

or

SELECT fieldname FROM NEmailNotes WHERE NEmailNotes.CC='ABC13'

so your SQL is looking for a field called from, hence the illegal result.
Hence the need for [] square brackets to resolve spaces in field names, and otherwise illegal terms.

SELECT [from] FROM NEmailNotes WHERE NEmailNotes.CC='ABC13'

 

[pr2-eugin]

What are you SELECTing FROM the Table? Your SQL should be something like

SELECT fieldName1, fieldName2, fieldName3
FROM yourTable
WHERE someField = 'Some String' AND someNumberField = 10 AND someDateField = #05/31/2015#

 

[Indigo]

I am actually trying work out two SQL statements. One from a query and one from a table.

the gist for both is:

            strSQL2 = "SELECT * FROM NEmailNotes " & _
" WHERE NEMailNotes.ShiftDate =#" & HoldDate & "#" And " NEMailNotes.CC='" & HoldCC & "'"

So I am selecting all fields from my NEmailNotes table where the shift date equals some date and the CC (cost code) equal some string.

None of my field names have spaces.... learned this lesson long ago.

 

[spikepl]

You have inverted the process.

- Create the query in DEsign view in the query builder.
- Swicth to SQL view to get the SQL
- Build that SQL in code (replacing variable names etc)
- get the SQL string back in the Immediate Window
- paste it into the SQl view and check the design

 

[Indigo]

spikepl, I thought that I had done all that to get to where I am :-(

Here it is from the query builder:

SELECT NEmailNotes.*, NEmailNotes.CostCentre, NEmailNotes.ShiftDate
FROM NEmailNotes
WHERE (((NEmailNotes.CostCentre)=[Forms]![frmSecurity]![EMPCC]) AND ((NEmailNotes.ShiftDate)=[Forms]![frmSecurity]![ShiftDate]));

I have been chipping away at this based on everyone's prompts and now this statement works:

strSQL2 = "SELECT NEmailNotes " & _
                      " WHERE NEmailNotes.CC='" & HoldCC & "'"

But I'm back to this not working and my original type mismatch error:

            strSQL2 = "SELECT * FROM NEmailNotes " & _
" WHERE NEMailNotes.ShiftDate =#" & HoldDate & "#" And " NEMailNotes.CC='" & HoldCC & "'"

Which tells me that my concatenation is messed up.... sigh

 

[spikepl]

so you check what it looks like in the immediate window and compare it to your original SQL which works in the query designer.

 

[spikepl]

for ease use hardcoded values in the query designer nstead of variable parmeters or references to controls - easier to replace in/compare to the sql you are making in vba

 

[Indigo]

Thank you for the tutorial.... I got it, finally....

strSQL2 = "SELECT * FROM NEMailNotes " & _
                      " WHERE NEMailNotes.ShiftDate = #" & HoldDate & "# And  NEMailNotes.CC='" & HoldCC & "'"