SQL Parameter in VBA Code

[jgier]

Hello Everyone,

I am having an issue with getting a parameter query to work in VBA code. I have the Between (Date) And (Date) to work but cannot get the >= (Date) to work. The date is based on table fields they enter in at night. I do not use a form since a form does not save dates entered upon close. Any suggestions would be much appreciated, Thanks in advance.

Set rs = New ADODB.Recordset
rs.Open _
"Select * FROM tblInputDate;", _
ActiveConnection:=CurrentProject.Connection

strSQL = "INSERT INTO tblGroupRenewal ( PLATFORM_CD, BLK_BUS_CD, GRP_NBR, MSTR_RPT_NBR, GRP_RESET_DT, GRP_TERM_DT ) " _
& "SELECT PLATFORM_CD, BLK_BUS_CD, Val([GRP_ID]) AS GRP_NBR, MSTR_RPT_NBR, GRP_RESET_DT, GRP_TERM_DT " _
& "FROM HUM_CPDGROUP " _
& "WHERE (((PLATFORM_CD)='EM') AND ((BLK_BUS_CD)='GR') AND ((GRP_RESET_DT) Between #" & rs("BeginDate") & "# And #" & rs("EndDate") & "#) AND ((GRP_TERM_DT) Is Null Or (GRP_TERM_DT) >= #" rs("TermDate") & "#)" - That is the area of error occurring, first portion works.

 

[Galaxiom]

Are your date criteria in mm/dd/yyyy format? They need to be for SQL so if you are not in North America you will need to change the format. If the text of the date is invalid in mm/dd/yyyy you will get an error.

BTW Do any of your date fields have a Time component? This is often a problem when dates are compared with greater than or equals and the records from the date are expected.

Also did you really mean to select a random record into the recordset to use as the criteria? Without an Order By the first record in the recordset won't be reliably determined.

 

[jzwp22]

If you do not already have it, I would put a debug.print statement after building the query. This will push the actual query text to the VBA immediate window so that you could see if the parameters are populating correctly. You can of course copy and paste that text to a new query and test it also.

Set rs = New ADODB.Recordset
rs.Open _
"Select * FROM tblInputDate;", _
ActiveConnection:=CurrentProject.Connection

strSQL = "INSERT INTO tblGroupRenewal ( PLATFORM_CD, BLK_BUS_CD, GRP_NBR, MSTR_RPT_NBR, GRP_RESET_DT, GRP_TERM_DT ) " _
& "SELECT PLATFORM_CD, BLK_BUS_CD, Val([GRP_ID]) AS GRP_NBR, MSTR_RPT_NBR, GRP_RESET_DT, GRP_TERM_DT " _
& "FROM HUM_CPDGROUP " _
& "WHERE (((PLATFORM_CD)='EM') AND ((BLK_BUS_CD)='GR') AND ((GRP_RESET_DT) Between #" & rs("BeginDate") & "# And #" & rs("EndDate") & "#) AND ((GRP_TERM_DT) Is Null Or (GRP_TERM_DT) >= #" rs("TermDate") & "#)"

debug.print strSQL
...rest of code

Typically I use the syntax rs!BeginDate rather than rs("BeginDate"). I do not know if that would matter.

 

[jgier]

@jzwp22, @Galaxiom Thanks for the quick reply. I looked into it a little more thoroughly and did each section at a time and was able to get the code to work. The code is below:

strSQL = "INSERT INTO tblGroupRenewal ( PLATFORM_CD, BLK_BUS_CD, GRP_NBR, MSTR_RPT_NBR, GRP_RESET_DT, GRP_TERM_DT ) " _
& "SELECT PLATFORM_CD, BLK_BUS_CD, Val([GRP_ID]) AS GRP_NBR, MSTR_RPT_NBR, GRP_RESET_DT, GRP_TERM_DT " _
& "FROM HUM_CPDGROUP " _
& "WHERE (((PLATFORM_CD)='EM') AND ((BLK_BUS_CD)='GR') AND ((GRP_RESET_DT) Between #" & rs("BeginDate") & "# And #" & rs("EndDate") & "#) AND (((GRP_TERM_DT) Is Null) OR (GRP_TERM_DT) >= #" & rs("TermDate") & "#))"

 

[jzwp22]

Glad you got it worked out!