Date Diff Function

[Guest]

Hello,
I have a table that contain period begin date and period enddate as follows:
1106 11/01/ 2006 11/30/2006
1206 12/01/2006 12/31/2006
0107 01/01/2007 01/31/2007 etc

I am trying to do a rolling 12 month and created the following vba code but
I am getting an error that too few parameters. If I replace the
Forms!frmFinancial!cboEndDate with say #12/31/2006#, it runs okay. I guess I
need some help on the syntax - the result should be Mth1, Mth2 etc . The
begindate and enddate are two variables in the procedure.


pstrSQL = "SELECT pe, pebedt, peendt, Mth &
DateDiff('m',pebedt,Forms!frmFinancial!cboEndDate) " & _
"into pemonths FROM dbo_periodt " & _
"WHERE petime= 'CL' pebedt between " & "#" & begindate & "#" & " And
" & "#" & enddate & "#"

 

[Guest]

Is the data in Forms!frmFinancial!cboEndDate a date or text? You might change
it to something like this:

CDate(Forms!frmFinancial!cboEndDate)

How many columns are in cboEndDate? Not how may do you see, but rather, that
in the Column Count format property of the combo box? You could be pointing
at the wrong column and need to use the Column property to point to the right
one. If memory serves, the first combo box column is 0 and not 1.

 

[Guest]

Hi Jerry,

The combo box is actually populated from a query that reads a date field
from the dbo_periodt table and the column count format property is one.

I tried the Cdate, the variable endate (which I assigned the cboEndDate) but
it seems that the datediff function is not reading the variable - might be
the syntax???