VBA SQL statement form parameter not generating Value?

[dowlingm815]

The following code is supposed to grab the PO Amt and group the data with the date parameters from a form. However, the coded finds no data. If the date parameter from the form is removed, the code will find data to group.

Any suggestions would be appreciated.

Mary

Private Sub sql_Step04_b1_Extract_POScreenAmt()

On Error GoTo Err_Hndlr

'**********************************************
Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef

'set variable values
Set dbs = CurrentDb
strQueryName = "sql_Step04_b1_Extract_POScreenAmt"

'Delete old query first - we want fresh data!
dbs.QueryDefs.Delete strQueryName

'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.


strSQL = "SELECT POCompletedScreen.[PO #], " & _
            "POCompletedScreen.[PO Total], " & _
            "DateValue([Creation Date]) AS [Creation Date2]" & _
         "FROM POCompletedScreen " & _
         "GROUP BY POCompletedScreen.[PO #], " & _
         "POCompletedScreen.[PO Total], " & _
            "DateValue([Creation Date]) " & _
         "HAVING (((DateValue([Creation Date])) Between [Forms]![F_Waiver_Yr]![txb_date_start] And [Forms]![F_Waiver_Yr]![txb_date_end]))" & _
         "ORDER BY POCompletedScreen.[PO #];"

'Create query definition
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)



sql_Step04_b1_Extract_POScreenAmt_Exit:
  Exit Sub


Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "sql_Step04_b1_Extract_POScreenAmt()"
End Sub

[Mariostg]

in you HAVING clause, did you try putting hard coded dates to see if it returns someting?
I am not sure about using parameter values, but normally, dates must be enclosed between #. Other wise something like 2010-03-04 will do 2010 minus 3 minus 4...

[dowlingm815]

Yes, i tested with hard coded dates and it worked. however, the dates are variable.

[Mariostg]

After you define strSQL, can you put a line

Debug.Print strSQL

so you/we can see exactly what the query looks like after you enter the parameters in the form.
the output will be visible in the Immediate Window. You can see it by doing Ctrl+g.
If the query does not return any error, just blank output, it is the having clause that basically eliminates all records...

[dowlingm815]

in the immediate window, the

?[Forms]![F_Waiver_Yr]![txb_date_start]
?[Forms]![F_Waiver_Yr]![txb_date_end]

returns, the selected date of 06/01/2010 and 06/30/2010.

this is what the debug.print returns for the SQL statement:

SELECT POCompletedScreen.[PO #], POCompletedScreen.[PO Total], DateValue([Creation Date]) AS [Creation Date2]FROM POCompletedScreen GROUP BY POCompletedScreen.[PO #], POCompletedScreen.[PO Total], DateValue([Creation Date]) HAVING (((DateValue([Creation Date])) Between [Forms]![F_Waiver_Yr]![txb_date_start] And [Forms]![F_Waiver_Yr]![txb_date_end]))ORDER BY POCompletedScreen.[PO #];

[Mariostg]

Ah no wonder, it is like the [Form]... statements are taken as a pure string, you want that:

strSQL = "SELECT POCompletedScreen.[PO #], " & _
            "POCompletedScreen.[PO Total], " & _
            "DateValue([Creation Date]) AS [Creation Date2]" & _
         "FROM POCompletedScreen " & _
         "GROUP BY POCompletedScreen.[PO #], " & _
         "POCompletedScreen.[PO Total], " & _
            "DateValue([Creation Date]) " & _
         "HAVING (((DateValue([Creation Date])) Between" & _
         [Forms]![F_Waiver_Yr]![txb_date_start] & " And " & _
         [Forms]![F_Waiver_Yr]![txb_date_end] & ")) ORDER BY POCompletedScreen.[PO #];"

[dowlingm815]

i'm not following you, you are implying that "[Forms]![F_Waiver_Yr]![txb_date_start]" converts itself into a string.

however, i have a similar statement works which is executed prior to this sql statement. the other code is as follows:

Private Sub sql_Step01_a_PO_MONTH()

On Error GoTo Err_Hndlr

'**********************************************
Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef

'set variable values
Set dbs = CurrentDb
strQueryName = "sql_Step01_a_PO_MONTH"

'Delete old query first - we want fresh data!
dbs.QueryDefs.Delete strQueryName

'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.


strSQL = "SELECT FIMSMGR_FPBPOHD.FPBPOHD_CODE, FIMSMGR_FPBPOHD.FPBPOHD_TRANS_DATE, FIMSMGR_FPBPOHD.FPBPOHD_PO_DATE, FIMSMGR_FPBPOHD.FPBPOHD_CHANGE_SEQ_NUM, FIMSMGR_FPBPOHD.FPBPOHD_PCLS_CODE, FIMSMGR_FPBREQH.FPBREQH_DOC_REF_CODE, FIMSMGR_FPBPOHD.FPBPOHD_BUYR_CODE, FIMSMGR_FPRPODT.FPRPODT_COMM_DESC, Sum(FIMSMGR_FPRPODA.FPRPODA_AMT) AS SumOfFPRPODA_AMT, FIMSMGR_FPRPODT.FPRPODT_COMM_CODE " & _
        "FROM FIMSMGR_FPRPODA " & _
        "INNER JOIN (FIMSMGR_FPRPODT INNER JOIN " & _
            "((FIMSMGR_FTVRQPO RIGHT JOIN FIMSMGR_FPBPOHD ON FIMSMGR_FTVRQPO.FTVRQPO_POHD_CODE = FIMSMGR_FPBPOHD.FPBPOHD_CODE) " & _
            "LEFT JOIN FIMSMGR_FPBREQH ON FIMSMGR_FTVRQPO.FTVRQPO_REQD_CODE = FIMSMGR_FPBREQH.FPBREQH_CODE) " & _
            "ON FIMSMGR_FPRPODT.FPRPODT_POHD_CODE = FIMSMGR_FPBPOHD.FPBPOHD_CODE) " & _
            "ON (FIMSMGR_FPRPODA.FPRPODA_POHD_CODE = FIMSMGR_FPRPODT.FPRPODT_POHD_CODE) " & _
            "AND (FIMSMGR_FPRPODA.FPRPODA_POHD_CODE = FIMSMGR_FPBPOHD.FPBPOHD_CODE) " & _
            "AND (FIMSMGR_FPRPODA.FPRPODA_CHANGE_SEQ_NUM = FIMSMGR_FPBPOHD.FPBPOHD_CHANGE_SEQ_NUM) AND (FIMSMGR_FPRPODA.FPRPODA_CHANGE_SEQ_NUM = FIMSMGR_FPRPODT.FPRPODT_CHANGE_SEQ_NUM) " & _
        "GROUP BY FIMSMGR_FPBPOHD.FPBPOHD_CODE, FIMSMGR_FPBPOHD.FPBPOHD_TRANS_DATE, FIMSMGR_FPBPOHD.FPBPOHD_PO_DATE, FIMSMGR_FPBPOHD.FPBPOHD_CHANGE_SEQ_NUM, FIMSMGR_FPBPOHD.FPBPOHD_PCLS_CODE, FIMSMGR_FPBREQH.FPBREQH_DOC_REF_CODE, FIMSMGR_FPBPOHD.FPBPOHD_BUYR_CODE, FIMSMGR_FPRPODT.FPRPODT_COMM_DESC, FIMSMGR_FPRPODT.FPRPODT_COMM_CODE, FIMSMGR_FPRPODT.FPRPODT_ITEM " & _
        "HAVING (((FIMSMGR_FPBPOHD.FPBPOHD_TRANS_DATE) Between [Forms]![F_Waiver_Yr]![txb_date_start] And [Forms]![F_Waiver_Yr]![txb_date_end]) AND ((FIMSMGR_FPRPODT.FPRPODT_ITEM)=1))" & _
        "ORDER BY FIMSMGR_FPBPOHD.FPBPOHD_TRANS_DATE DESC;"


'Create query definition
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)



sql_Step01_a_PO_MONTH_Exit:
  Exit Sub


Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "sql_Step01_a_PO_MONTH()"
End Sub

[Mariostg]

Ok you can disregard my last, it works differently when you use a query def than when you use a domcd.runsql which I am used to.

Not sure now why it would not work at the moment. I will sleep on that tonight.

[MMcCarthy]

You are missing #'s to define your start and end date.

strSQL = "SELECT POCompletedScreen.[PO #], " & _
            "POCompletedScreen.[PO Total], " & _
            "DateValue([Creation Date]) AS [Creation Date2]" & _
         "FROM POCompletedScreen " & _
         "GROUP BY POCompletedScreen.[PO #], " & _
         "POCompletedScreen.[PO Total], " & _
            "DateValue([Creation Date]) " & _
         "HAVING (((DateValue([Creation Date])) Between #[Forms]![F_Waiver_Yr]![txb_date_start]# And #[Forms]![F_Waiver_Yr]![txb_date_end]#))" & _
         "ORDER BY POCompletedScreen.[PO #];"

[dowlingm815]

nope, the # has no affect on it. i tried and got the same results. moreover, it works on the other sql statements.

[colintis]

Can you tell me a bit about the [Creation Date] you used in the DateValue? What format is inside this field? The problem might comes from it.

[dowlingm815]

Good morning,

The [Creation date] is a date type field. Originally, it was a string but is was converted in the back-end.

[dowlingm815]

From what i can gather, the vba statement looks at the form parameter as a string therefore, it needs to be qualified as such:

Between #" & [Forms]![F_Waiver_Yr]![txb_date_start] # And #" & [Forms]![F_Waiver_Yr]![txb_date_end] & "#

[colintis]

When you look into the [Creation date] from the table view, is it simply in the format of mm/dd/yyyy?

Normally by default, SQL recogned date fields as mm/dd/yyyy format, so is your [Creation date] originally a DATE/TIME datatype? if so then you try the code again without DateValue

[dowlingm815]

the DateValue was dropped after, the code was corrected with the above which is re-posted below. i agree less code the more efficient. thank you for your assistance.

mary

Between #" & [Forms]![F_Waiver_Yr]![txb_date_start] # And #" & [Forms]![F_Waiver_Yr]![txb_date_end] & "#