Converting query to string

[Col]

Hi,
can anyone tell me how to enter the following
WHERE clause into a string in VBA?

[Request Date] BETWEEN (DateAdd("ww", -1, Forms![Weekly Summary]![cboWeek])) AND Forms![Weekly Summary]![cboWeek]

I need to store this as a string in order to set the form's filter from code.

Thanks in advance

 

[shacket]

You would just set the string to it.

Dim YourSQL As String
YourSQL = "[Request Date] BETWEEN (DateAdd("ww", -1, Forms![Weekly Summary]![cboWeek])) AND Forms![Weekly Summary]![cboWeek]"

Then call it this way:

[Forms]![Weekly Summary].RecordSource = "SELECT * FROM [YourTableName] WHERE " & YourSQL

HTH

[This message has been edited by shacket (edited 09-26-2001).]

 

[Pat Hartman]

If you have more than a single year's worth of data in your table, you'll need to include year in your selection criteria.

 

[Col]

Trouble is that you can't just enter the string as suggested - it interprets it as stopping at the second " mark (ie before the ww in the DateAdd function). I'm just wondering how to include the reserved operators (such as ") in the code.

 

[MikeAngelastro]

Col,

Create a constant

Public Const QUOTES = """"

Then when you need quotation marks all you have to do is insert & QUOTES & instead of putting in quotation marks. It works for me. It's also cleaner looking.

Mike

 

[MikeAngelastro]

Hi,

My previous reply was not complete. See code below:

dim strCompany as string
dim strSQL as string

strSQL = "SELECT * FROM table WHERE Company = " & QUOTES & strCompany & QUOTES

This is needed because strCompany is a string.

Thanks,

Mike