SQL in VBA Filter form 2

[0212]

Hi, I hope someone can straighten me out (and my code)! I've looked at several books and each book shows me a different method to write SQL in VBA. I am very confused. There must be a simple, understandable method to develop a "where" clause for filtering a form using a dialogue box. I think I have the basics down for the following code. But I get an error (and probably more). Thanks for any help that you can give on both the code and providing insight or a referal to a good reference source for writing sql in vba. Thanks!

Private Sub Ok_Click()
'The purpose of this sub is to open FrmTrackRptDatesODR Form with QryRptsSelectODR Query as datasource'
'using filter input from this dialogue box. The input includes PE_ID (integer), BU_ID (integer),'
'RptType ( 3 Options), and OvrDue (3 Options).'
Me.Visible = False
Dim strWhere As String
strWhere = "1=1 "
'Set Where Clause variable to True'
'Find all records with the BU_ID and PE_ID input from Dialogue Box'
strWhere = strWhere & " AND [PEID]=" & Me![PE_ID] & _
" AND [BUID]=" & Me![BU_ID]
'For Case RptType in dialogue box, find all RA reports, CSP reports and RAA reports'
Select Case Me.RptType
Case 1
'For Case Ovrdue Rpts in dialogue Box, find all dates for RADue <31 days for today, < 30 days and'
'all null due dates'
Select Case Me.OvrDue
Case 1
'StrWhere = strWhere + "SQL Statement #Date# & _ (line continuation)'
strWhere = strWhere & " AND [RADue]<#(Date()+31)#" & _
" AND [RAComp]Is Null "";"
Case 2
strWhere = strWhere & " AND [RADue] >#(Date()+30)#" & _
" AND [RAComp]Is Null "";"
Case 3
strWhere = strWhere & " AND [RADue] Is Null" & _
" AND [RAComp]Is Null "";"
End Select
Case 2
'For Case Ovrdue Rpts find all dates for CSPDue <31 days for today, < 30 days and'
'all null due dates'
Select Case Me.OvrDue
Case 1
'StrWhere = strWhere + "SQL Statement #Date# & _ (line continuation)'
strWhere = strWhere & " AND [CSPDue] <#(Date()+31)#" & _
" AND [CSPComp]Is Null "";"
Case 2
strWhere = strWhere & " AND [CSPDue] >#(Date()+30)#" & _
" AND [CSPComp]Is Null "";"
Case 3
strWhere = strWhere & " AND [CSPDue] Is Null" & _
" AND [CSPComp]Is Null "";"
End Select
Case 3
'For Case Ovrdue Rpts find all dates for RAADue <31 days for today, < 30 days and'
'all null due dates'
Select Case Me.OvrDue
Case 1
'StrWhere = strWhere + "SQL Statement #Date# & _ (line continuation)'
strWhere = strWhere & " AND [RAADue] <#(Date()+31)#" & _
" AND [RAAComp]Is Null "";"
Case 2
strWhere = strWhere & " AND [RAADue] >#(Date()+30)#" & _
" AND [RAAComp]Is Null "";"
Case 3
strWhere = strWhere & " AND [RAADue] Is Null" & _
" AND [RAAComp]Is Null "";"
End Select
End Select

 

[ZmrAbdulla]

Try

http://www.fontstuff.com/access/acctut15.htm

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[0212]

Thank you, ZmrAbdulla! I have seen this sight. I guess I am not sure when to use a string variable to add on sql statements and the correct syntax within vba. Also, the following has an error. Also, is the current date variable correct? Thanks for you help!

strWhere = strWhere & " AND [RADue]<#(Date()+31)#" & _
" AND [RAComp]Is Null "";"

 

[BSman]

I think you need to use the DateAdd() function if you want to modify the day (add 31 days?).

Bob

 

[PHV]

And what about this ?
strWhere = strWhere & " AND [RADue]<(Date()+31)" & _
" AND [RAComp]Is Null "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[0212]

Thank you, PHV! I guess I don't know when to use the hashmarks (#). Can you explain and little? Thanks!

Jim.

 

[PHV]

The hashmarks are required to delimit a date constant

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[0212]

Thanks, again, PHV, for you help!

 

[ZmrAbdulla]

Thanks for the follow up PHV

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[mbarkley9872]

ZmrAbdulla,

Great site, thanks!