Enter month as Jan rather then 1/1/2006

[Joe70]

I have a query that does a monthly report using...
Between [BeginningDate] And [EndingDate]
in the Criteria area of the date field.
I would like to just enter the month as Jan or Feb etc. to get the
monthly report. How can this be done?

 

[Guest]

At a minimum you would need to enter the month and year values ...
You could use the DateSerial() function to create a valid date entry for
these 2 bits of information ..

Example:
Between DateSerial([Enter Year],[Enter Month],1) And DateSerial([Enter
Year],[Enter Month]+1,0)

R. Hicks

 

[John Vinson]

I have a query that does a monthly report using...
Between [BeginningDate] And [EndingDate]
in the Criteria area of the date field.
I would like to just enter the month as Jan or Feb etc. to get the
monthly report. How can this be done?

If you always want the current year (i.e. typing in Dec during 2006
will give you Dec 2006, not "last month") you can use a criterion on
the datefield

= "#" & [Enter month:] & " 1#" AND

< DateAdd("m", 1, "#" & [Enter month:] & " 1#")

This will parse to

= #Jan 1# AND < 2/1/2006

since Access will fill in the current year if given only a month and
day.

John W. Vinson[MVP]

 

[Joe70]

I have a query that does a monthly report using...
Between [BeginningDate] And [EndingDate]
in the Criteria area of the date field.
I would like to just enter the month as Jan or Feb etc. to get the
monthly report. How can this be done?

If you always want the current year (i.e. typing in Dec during 2006
will give you Dec 2006, not "last month") you can use a criterion on
the datefield

= "#" & [Enter month:] & " 1#" AND

< DateAdd("m", 1, "#" & [Enter month:] & " 1#")

This will parse to

= #Jan 1# AND < 2/1/2006

since Access will fill in the current year if given only a month and
day.

John W. Vinson[MVP]

I tried the code, the window came up and I typed Jan into it. I then
got an error message.

This expression is typed incorrectly, or it is too complex to be
evaluated...

 

[John Vinson]

I tried the code, the window came up and I typed Jan into it. I then
got an error message.

This expression is typed incorrectly, or it is too complex to be
evaluated...

Please open the query in SQL view and post the SQL text here.

John W. Vinson[MVP]

 

[Joe70]

Please open the query in SQL view and post the SQL text here.

John W. Vinson[MVP]

SELECT METH98.INV_DATE, METH98.CUSTOMER, METH98.INV_NUMBER,
METH98.SUBLET, IIf([TYPE]="EXEMPT",[PARTS],"0") AS EXEMPT,
IIf([TYPE]="RESALE",[PARTS],"0") AS RESALE,
IIf([TYPE]="TAXABLE",[PARTS],"0") AS TAXABLE, METH98.LABOR,
([TAXABLE]*0.05) AS TAX,
Nz([Sublet],0)+Nz([EXEMPT],0)+Nz([RESALE],0)+Nz([TAXABLE],0)+Nz([Labor],0)+Nz([Tax],0)
AS TOTAL
FROM METH98
WHERE (((METH98.INV_DATE)>="#" & [Enter month:] & " 1#" And
(METH98.INV_DATE)<DateAdd("m",1,"#" & [Enter month:] & " 1#")))
ORDER BY METH98.INV_NUMBER;

 

[Joe70]

I have a query that does a monthly report using...
Between [BeginningDate] And [EndingDate]
in the Criteria area of the date field.
I would like to just enter the month as Jan or Feb etc. to get the
monthly report. How can this be done?

If you always want the current year (i.e. typing in Dec during 2006
will give you Dec 2006, not "last month") you can use a criterion on
the datefield

= "#" & [Enter month:] & " 1#" AND

< DateAdd("m", 1, "#" & [Enter month:] & " 1#")

This will parse to

= #Jan 1# AND < 2/1/2006

since Access will fill in the current year if given only a month and
day.

John W. Vinson[MVP]

I still cannot get this to work. Not sure if you seen my last message
but I will post the SQL again.

SELECT METH98.INV_DATE, METH98.CUSTOMER, METH98.INV_NUMBER,
METH98.SUBLET, IIf([TYPE]="EXEMPT",[PARTS],"0") AS EXEMPT,
IIf([TYPE]="RESALE",[PARTS],"0") AS RESALE,
IIf([TYPE]="TAXABLE",[PARTS],"0") AS TAXABLE, METH98.LABOR,
([TAXABLE]*0.05) AS TAX,
Nz([Sublet],0)+Nz([EXEMPT],0)+Nz([RESALE],0)+Nz([TAXABLE],0)+Nz([Labor],0)+Nz([Tax],0)
AS TOTAL
FROM METH98
WHERE (((METH98.INV_DATE)>="#" & [Enter month:] & " 1#" And
(METH98.INV_DATE)<DateAdd("m",1,"#" & [Enter month:] & " 1#")))
ORDER BY METH98.INV_NUMBER;