Date Diff Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

deodev - 21 May 2007 19:05 GMT

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 & "#"

Signature

deodev

Reply to this Message

Jerry Whittle - 21 May 2007 19:59 GMT

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.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Hello,
> I have a table that contain period begin date and period enddate as follows:
[quoted text clipped - 13 lines]
> "WHERE petime= 'CL' pebedt between " & "#" & begindate & "#" & " And
> " & "#" & enddate & "#"

Reply to this Message

deodev - 22 May 2007 15:06 GMT

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???

Signature

deodev

> Is the data in Forms!frmFinancial!cboEndDate a date or text? You might change
> it to something like this:
[quoted text clipped - 23 lines]
> > "WHERE petime= 'CL' pebedt between " & "#" & begindate & "#" & " And
> > " & "#" & enddate & "#"

Reply to this Message