format sql statement

  • Thread starter Thread starter T
  • Start date Start date
T

T

I have a listbox which has the fields amendment and original. The
field values are true or false. I use these fields to merge into word
depending on their values.

I've created a query to give me the values, but I need to go through
the recordset to merge them into Word.

I'm having trouble with the select statement format. It highlights the
last paragraph. Help? Thanks

strSQL1 = "SELECT AccountUse.[Customer ID], AccountUse.[Holder ID],
AccountUse.AccountNumber, " & _
"AccountUse.AccountUseID, AccountUse.[Category ID],
AcctCategory.[Category Name], " & _
"AccountUse.Active, AccountUse.Original, AccountUse.Amendment "
& _
"FROM AcctCategory INNER JOIN AccountUse ON
AcctCategory.[Category ID] = AccountUse.[Category ID] " & _
"WHERE AccountUse.[Customer ID]= " &
[Forms]![Main]![txtselcust] & " AND " & _
"AccountUse.[Holder ID] = " & [Forms]![Main]![txtAcctHolderID]
& " AND " & _
"AcctCategory.[Category Name]= 'POS' & " AND " & _
"AccountUse.Active= 'True' & '"
 
Omit the quotes around True. True is a defined value in SQL, i.e. you are
not matching text.

"WHERE (AccountUse.[Customer ID]= " & [Forms]![Main]![txtselcust] & _
") AND (AccountUse.[Holder ID] = " & [Forms]![Main]![txtAcctHolderID] & _
") AND (AcctCategory.[Category Name]= 'POS') AND (AccountUse.Active =
True);"
 
Allen said:
Omit the quotes around True. True is a defined value in SQL, i.e. you are
not matching text.

"WHERE (AccountUse.[Customer ID]= " & [Forms]![Main]![txtselcust] & _
") AND (AccountUse.[Holder ID] = " & [Forms]![Main]![txtAcctHolderID] & _
") AND (AcctCategory.[Category Name]= 'POS') AND (AccountUse.Active =
True);"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

T said:
I have a listbox which has the fields amendment and original. The
field values are true or false. I use these fields to merge into word
depending on their values.

I've created a query to give me the values, but I need to go through
the recordset to merge them into Word.

I'm having trouble with the select statement format. It highlights the
last paragraph. Help? Thanks

strSQL1 = "SELECT AccountUse.[Customer ID], AccountUse.[Holder ID],
AccountUse.AccountNumber, " & _
"AccountUse.AccountUseID, AccountUse.[Category ID],
AcctCategory.[Category Name], " & _
"AccountUse.Active, AccountUse.Original, AccountUse.Amendment "
& _
"FROM AcctCategory INNER JOIN AccountUse ON
AcctCategory.[Category ID] = AccountUse.[Category ID] " & _
"WHERE AccountUse.[Customer ID]= " &
[Forms]![Main]![txtselcust] & " AND " & _
"AccountUse.[Holder ID] = " & [Forms]![Main]![txtAcctHolderID]
& " AND " & _
"AcctCategory.[Category Name]= 'POS' & " AND " & _
"AccountUse.Active= 'True' & '"
Click to expand...
Click to expand...

Allen thanks so much for the quick response. Now it highlights the
true.

strSQL1 = "SELECT AccountUse.[Customer ID], AccountUse.[Holder ID],
AccountUse.AccountNumber, " & _
"AccountUse.AccountUseID, AccountUse.[Category ID],
AcctCategory.[Category Name], " & _
"AccountUse.Active, AccountUse.Original, AccountUse.Amendment "
& _
"FROM AcctCategory INNER JOIN AccountUse ON
AcctCategory.[Category ID] = AccountUse.[Category ID] " & _
"WHERE (AccountUse.[Customer ID]= " &
[Forms]![Main]![txtselcust] & _
") AND (AccountUse.[Holder ID] = " &
[Forms]![Main]![txtAcctHolderID] & _
") AND (AcctCategory.[Category Name]= 'POS') AND
(AccountUse.Active = " True);"
 
Your example still has quotes around the "True" at the end.

Perhaps you could mock up a query, and type some dummy literal values into
the Criteria row in query design. Test. Once you have it working, switch the
query to SQL View (View menu), and see the string you need to create.

Then in your code, after you create the string, add the line:
Debug.Print strSQL1
Now when it fails, open the Immediate Window (Ctrl+G) and see what's wrong
with the string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

T said:
Allen said:
Omit the quotes around True. True is a defined value in SQL, i.e. you are
not matching text.

"WHERE (AccountUse.[Customer ID]= " & [Forms]![Main]![txtselcust] & _
") AND (AccountUse.[Holder ID] = " & [Forms]![Main]![txtAcctHolderID] & _
") AND (AcctCategory.[Category Name]= 'POS') AND (AccountUse.Active =
True);"

T said:
I have a listbox which has the fields amendment and original. The
field values are true or false. I use these fields to merge into word
depending on their values.

I've created a query to give me the values, but I need to go through
the recordset to merge them into Word.

I'm having trouble with the select statement format. It highlights the
last paragraph. Help? Thanks

strSQL1 = "SELECT AccountUse.[Customer ID], AccountUse.[Holder ID],
AccountUse.AccountNumber, " & _
"AccountUse.AccountUseID, AccountUse.[Category ID],
AcctCategory.[Category Name], " & _
"AccountUse.Active, AccountUse.Original, AccountUse.Amendment "
& _
"FROM AcctCategory INNER JOIN AccountUse ON
AcctCategory.[Category ID] = AccountUse.[Category ID] " & _
"WHERE AccountUse.[Customer ID]= " &
[Forms]![Main]![txtselcust] & " AND " & _
"AccountUse.[Holder ID] = " & [Forms]![Main]![txtAcctHolderID]
& " AND " & _
"AcctCategory.[Category Name]= 'POS' & " AND " & _
"AccountUse.Active= 'True' & '"
Click to expand...
Click to expand...

Allen thanks so much for the quick response. Now it highlights the
true.

strSQL1 = "SELECT AccountUse.[Customer ID], AccountUse.[Holder ID],
AccountUse.AccountNumber, " & _
"AccountUse.AccountUseID, AccountUse.[Category ID],
AcctCategory.[Category Name], " & _
"AccountUse.Active, AccountUse.Original, AccountUse.Amendment "
& _
"FROM AcctCategory INNER JOIN AccountUse ON
AcctCategory.[Category ID] = AccountUse.[Category ID] " & _
"WHERE (AccountUse.[Customer ID]= " &
[Forms]![Main]![txtselcust] & _
") AND (AccountUse.[Holder ID] = " &
[Forms]![Main]![txtAcctHolderID] & _
") AND (AcctCategory.[Category Name]= 'POS') AND
(AccountUse.Active = " True);"
Click to expand...
 
Allen said:
Your example still has quotes around the "True" at the end.

Perhaps you could mock up a query, and type some dummy literal values into
the Criteria row in query design. Test. Once you have it working, switch the
query to SQL View (View menu), and see the string you need to create.

Then in your code, after you create the string, add the line:
Debug.Print strSQL1
Now when it fails, open the Immediate Window (Ctrl+G) and see what's wrong
with the string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

T said:
Allen said:
Omit the quotes around True. True is a defined value in SQL, i.e. you are
not matching text.

"WHERE (AccountUse.[Customer ID]= " & [Forms]![Main]![txtselcust] & _
") AND (AccountUse.[Holder ID] = " & [Forms]![Main]![txtAcctHolderID] & _
") AND (AcctCategory.[Category Name]= 'POS') AND (AccountUse.Active =
True);"

I have a listbox which has the fields amendment and original. The
field values are true or false. I use these fields to merge into word
depending on their values.

I've created a query to give me the values, but I need to go through
the recordset to merge them into Word.

I'm having trouble with the select statement format. It highlights the
last paragraph. Help? Thanks

strSQL1 = "SELECT AccountUse.[Customer ID], AccountUse.[Holder ID],
AccountUse.AccountNumber, " & _
"AccountUse.AccountUseID, AccountUse.[Category ID],
AcctCategory.[Category Name], " & _
"AccountUse.Active, AccountUse.Original, AccountUse.Amendment "
& _
"FROM AcctCategory INNER JOIN AccountUse ON
AcctCategory.[Category ID] = AccountUse.[Category ID] " & _
"WHERE AccountUse.[Customer ID]= " &
[Forms]![Main]![txtselcust] & " AND " & _
"AccountUse.[Holder ID] = " & [Forms]![Main]![txtAcctHolderID]
& " AND " & _
"AcctCategory.[Category Name]= 'POS' & " AND " & _
"AccountUse.Active= 'True' & '"
Click to expand...

Allen thanks so much for the quick response. Now it highlights the
true.

strSQL1 = "SELECT AccountUse.[Customer ID], AccountUse.[Holder ID],
AccountUse.AccountNumber, " & _
"AccountUse.AccountUseID, AccountUse.[Category ID],
AcctCategory.[Category Name], " & _
"AccountUse.Active, AccountUse.Original, AccountUse.Amendment "
& _
"FROM AcctCategory INNER JOIN AccountUse ON
AcctCategory.[Category ID] = AccountUse.[Category ID] " & _
"WHERE (AccountUse.[Customer ID]= " &
[Forms]![Main]![txtselcust] & _
") AND (AccountUse.[Holder ID] = " &
[Forms]![Main]![txtAcctHolderID] & _
") AND (AcctCategory.[Category Name]= 'POS') AND
(AccountUse.Active = " True);"
Click to expand...
Click to expand...

You are so right. I took the " out and it works. Sorry about the
blunder and thanks so much!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi Value Selection issues 4
Add up if then Statements 3
Access merge with Word 3
If Then Else Statement 4
Filter sub form with Combo box 1
Using For...Each...Next Statement 2
Insert Into problem? 3
SQL Statement wording 2

Back
Top