Enter Month as Jan rather then 1/1/2006 -- Please answer

J

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?
Click to expand...

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
Click to expand...
< DateAdd("m", 1, "#" & [Enter month:] & " 1#")

This will parse to
= #Jan 1# AND < 2/1/2006
Click to expand...

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

John W. Vinson[MVP]
Click to expand...


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;
 
J

John Vinson

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

Sorry Joe - been a bit under the weather.
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,
Click to expand...

If you'll be doing math with these calculated fields, use 0 rather
than "0" in the IIF. The text string "0" will force the value returned
by IIF to Text, and cause an extra conversion and possible errors.
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;
Click to expand...

Try changing the WHERE clause to

WHERE (((METH98.INV_DATE)>="#" & CDate([Enter month:] & " 1") & "#"
And
(METH98.INV_DATE)<DateAdd("m",1,CDate([Enter month:] & " 1")))


John W. Vinson[MVP]
 
J

Joe70

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

Sorry Joe - been a bit under the weather.
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,
Click to expand...

If you'll be doing math with these calculated fields, use 0 rather
than "0" in the IIF. The text string "0" will force the value returned
by IIF to Text, and cause an extra conversion and possible errors.
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;
Click to expand...

Try changing the WHERE clause to

WHERE (((METH98.INV_DATE)>="#" & CDate([Enter month:] & " 1") & "#"
And
(METH98.INV_DATE)<DateAdd("m",1,CDate([Enter month:] & " 1")))


John W. Vinson[MVP]
Click to expand...

Thanks for getting back to me, hope your feeling better. I did try
changing the code, but now I get an error when saving.
Syntax error (missing operator) in query expression. Here is the SQL
view.

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)>="#" & CDate([Enter month:] & " 1") &
"#"And(METH98.INV_DATE)<DateAdd("m",1,CDate([Enter month:] & " 1")))
ORDER BY METH98.INV_NUMBER;
 
J

John Vinson

Thanks for getting back to me, hope your feeling better. I did try
changing the code, but now I get an error when saving.
Syntax error (missing operator) in query expression. Here is the SQL
view.
Click to expand...

Misplaced quotemark - my fault, sorry!

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)>="#" & CDate([Enter month:] & " 1") &
"#" And (METH98.INV_DATE)<DateAdd("m",1,CDate([Enter month:] & " 1")))
ORDER BY METH98.INV_NUMBER;

John W. Vinson[MVP]
 
J

Joe70

Thanks for getting back to me, hope your feeling better. I did try
changing the code, but now I get an error when saving.
Syntax error (missing operator) in query expression. Here is the SQL
view.
Click to expand...

Misplaced quotemark - my fault, sorry!

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)>="#" & CDate([Enter month:] & " 1") &
"#" And (METH98.INV_DATE)<DateAdd("m",1,CDate([Enter month:] & " 1")))
ORDER BY METH98.INV_NUMBER;

John W. Vinson[MVP]
Click to expand...

Hi John, I'm still getting the same error. I will paste SQL view here
and will also attach a picture of it to this message.

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)>="#" & CDate([Enter month:] & " 1") &"#" And
(METH98.INV_DATE)<DateAdd("m",1,CDate([Enter month:] & " 1")))
ORDER BY METH98.INV_NUMBER;
 
J

John Vinson

Hi John, I'm still getting the same error. I will paste SQL view here
and will also attach a picture of it to this message.
Click to expand...

The picture contributed absolutely nothing other than to annoy people
with dialup connections. PLEASE don't post attachments in these
newsgroups!

Sorry again... I was working just on the newsgroup, not in Access. The
query should be

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 >= "#" & CDate([Enter month:] & " 1") & "#"
And
METH98.INV_DATE < DateAdd("m",1,CDate([Enter month:] & " 1"))
ORDER BY METH98.INV_NUMBER;

I'm not sure why it's easier to do it this way in the first place:
might you consider instead having a Combo Box on the form? This could
be bound to a little table with a single Date/Time field with one
record for each month for the next twenty years or so; set its Format
property to "mmm yy" and use a criterion

=Forms!YourFormName!ComboBoxName

and just let the user *pick* a date rather than having to type it in.

John W. Vinson[MVP]
 
J

Joe70

Hi John, I'm still getting the same error. I will paste SQL view here
and will also attach a picture of it to this message.
Click to expand...

The picture contributed absolutely nothing other than to annoy people
with dialup connections. PLEASE don't post attachments in these
newsgroups!

Sorry again... I was working just on the newsgroup, not in Access. The
query should be

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 >= "#" & CDate([Enter month:] & " 1") & "#"
And
METH98.INV_DATE < DateAdd("m",1,CDate([Enter month:] & " 1"))
ORDER BY METH98.INV_NUMBER;

I'm not sure why it's easier to do it this way in the first place:
might you consider instead having a Combo Box on the form? This could
be bound to a little table with a single Date/Time field with one
record for each month for the next twenty years or so; set its Format
property to "mmm yy" and use a criterion

=Forms!YourFormName!ComboBoxName

and just let the user *pick* a date rather than having to type it in.

John W. Vinson[MVP]
Click to expand...

Hi John, sorry for posting the picture. I didn't know that pictures
are not allowed here.

I'm still getting an error with that code. (This expression is typed
incorrectly, or it is too complex to be evaluated.)
For now I will go back and use my old expression (Between
[BeginningDate] And [EndingDate]) till I can figure this out.

Thanks for helping.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Enter month as Jan rather then 1/1/2006 6
Date Expressions & Calculations 3
looks different in firefox then explorer 1
LOOKIN G FOR EMPLOYMENT LOOK HERE!!!!!!!!!! 2

Top