Web Development

Ask something
Discussion Locked

Problem with SQL Query in Visual Basic

0 helpful
9 replies

by twhite

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

The following query works if I remove the "tblLocations.PrimaryFlag=-1" from the WHERE statement. I wanted to see if anyone could tell me what I am doing wrong. I have also tried "tblLocations.[PrimaryFlag]='-1'" and "(((tblLocations.PrimaryFlag)=-1))". I need to be able to filter for records with a -1 result for PrimaryLocation (Which is a Yes/No field in Microsoft Access 2003).

strSQL = "INSERT INTO tvwCustomers ( CustomerID, CFirst, CLast, " & _
"CustomerType, BusinessName, CCity, CState, PrimaryFlag ) " & _
"SELECT tblCustomers.CustomerID, tblCustomers.CFirst, tblCustomers.CLast, " & _
"tblCustomers.CustomerType, tblCustomers.BusinessName, tblLocations.CCity, " & _
" tblLocations.CState, tblLocations.PrimaryFlag " & _
"FROM tblLocations INNER JOIN tblCustomers ON tblLocations.CustomerID = tblCustomers.CustomerID " & _
"WHERE tblLocations.PrimaryFlag=-1 " & varSQLWhere

9 replies

by rw

The syntax;

INSERT INTO tvwCustomers ( CustomerID, CFirst, CLast, CustomerType, BusinessName, CCity, CState, PrimaryFlag )
SELECT tblCustomers.CustomerID, tblCustomers.CFirst, tblCustomers.CLast, tblCustomers.CustomerType, tblCustomers.BusinessName, tblLocations.CCity, tblLocations.CState, tblLocations.PrimaryFlag
FROM tblLocations INNER JOIN tblCustomers ON tblLocations.CustomerID = tblCustomers.CustomerID
WHERE tblLocations.PrimaryFlag=-1

is absolutely correct, is it possibly the 'varSQLWhere' sql string that is causing your error?

by dalmond

I suggest that you convert your Location.PrimaryFlag to a bit and then use 1 for True and 0 for false. Then write a procedure that converts it back to -1 for true and o for false in your program. This has worked for me.

by peteran

I would also agree that there might be a problem in the varSQLWhere variable. The question would be is there a defaulted AND clause in there at the start.
Debug by writing the whole strSQL to the debug window (Debug.print strSQL ) and break, check the output in the Debug window and CHECK the sql again (I would also break up the strSQL compile into multiple statements as it would read easier and include a " & vbNewline" after each one. This will make debugging much easier)

by dragon emperor

WHERE tblLocations.PrimaryFlag=True

Also, what are you storing in varSQLWhere? Make sure it had " AND " at the start.

This discussion is closed to new replies.