General discussion

Locked

Problem with SQL Query in Visual Basic

By twhite ·
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

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by rw In reply to Problem with SQL Query in ...

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?

Collapse -

by twhite In reply to

Poster rated this answer.

Collapse -

by dalmond In reply to Problem with SQL Query in ...

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.

Collapse -

by twhite In reply to

Poster rated this answer.

Collapse -

by peteran In reply to Problem with SQL Query in ...

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)

Collapse -

by twhite In reply to

Poster rated this answer.

Collapse -

by Dragon Emperor In reply to Problem with SQL Query in ...

WHERE tblLocations.PrimaryFlag=True

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

Collapse -

by twhite In reply to

Poster rated this answer.

Collapse -

by twhite In reply to Problem with SQL Query in ...

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums