Query Criteria / Expression

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

Hello,

I have a table with dates [Booked_Date] ranging from Oct
1,2003 to present (12/9/2003). I'm trying to create a
query that will pull in all dates from previous months
(Oct and Nov)dynamically. I've tried various syntax
expressions with no luck.

Attempted Example:

Format:([Booked_Date],"m") - 1

And unfortunatley, this does not work as well. Does
anyone have any suggestions?

Thanks.
 
It's hard for me to tell what you've posted: whether you're trying to use
the Format function, or whether you're trying to create a computed field
named Format due to the colon.

The Format function returns a string, so it probably isn't the most
appropriate way to get the month. Try the Month function
Month([Booked_Date]) or the DatePart function with a parameter of m
DatePart("m", [Booked_Date])

What exactly is your criteria for previous months, though? Do you always
want the current month plus the previous two months? Then try WHERE
[Booked_Date] BETWEEN DateSerial(Year(Date()), Month(Date()) - 2, 1) AND
Date()

If you're building your query through the graphical interface, simply put
DateSerial(Year(Date()), Month(Date()) - 2, 1) AND Date() as the criteria
under the [Booked_Date] field.
 
Basically I'm pulling orders from Oracle that are
considered as a "backorder". Backorders = any record from
previous months not just Oct and Nov (like my example).
It could constist of several months if not more. The
[Booked_Date] is a fixed date field from oracle. I was
trying to convert it into a month format to make it easier
to work with.



-----Original Message-----
It's hard for me to tell what you've posted: whether you're trying to use
the Format function, or whether you're trying to create a computed field
named Format due to the colon.

The Format function returns a string, so it probably isn't the most
appropriate way to get the month. Try the Month function
Month([Booked_Date]) or the DatePart function with a parameter of m
DatePart("m", [Booked_Date])

What exactly is your criteria for previous months, though? Do you always
want the current month plus the previous two months? Then try WHERE
[Booked_Date] BETWEEN DateSerial(Year(Date()), Month(Date ()) - 2, 1) AND
Date()

If you're building your query through the graphical interface, simply put
DateSerial(Year(Date()), Month(Date()) - 2, 1) AND Date() as the criteria
under the [Booked_Date] field.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Hello,

I have a table with dates [Booked_Date] ranging from Oct
1,2003 to present (12/9/2003). I'm trying to create a
query that will pull in all dates from previous months
(Oct and Nov)dynamically. I've tried various syntax
expressions with no luck.

Attempted Example:

Format:([Booked_Date],"m") - 1

And unfortunatley, this does not work as well. Does
anyone have any suggestions?

Thanks.
Click to expand...


.
Click to expand...
 
Have you tried the simple criteria

....
WHERE [Booked_Date] < DateSerial(Year(Date()), Month(Date()), 1)

--
HTH
Van T. Dinh
MVP (Access)



Tony said:
Basically I'm pulling orders from Oracle that are
considered as a "backorder". Backorders = any record from
previous months not just Oct and Nov (like my example).
It could constist of several months if not more. The
[Booked_Date] is a fixed date field from oracle. I was
trying to convert it into a month format to make it easier
to work with.



-----Original Message-----
It's hard for me to tell what you've posted: whether you're trying to use
the Format function, or whether you're trying to create a computed field
named Format due to the colon.

The Format function returns a string, so it probably isn't the most
appropriate way to get the month. Try the Month function
Month([Booked_Date]) or the DatePart function with a parameter of m
DatePart("m", [Booked_Date])

What exactly is your criteria for previous months, though? Do you always
want the current month plus the previous two months? Then try WHERE
[Booked_Date] BETWEEN DateSerial(Year(Date()), Month(Date ()) - 2, 1) AND
Date()

If you're building your query through the graphical interface, simply put
DateSerial(Year(Date()), Month(Date()) - 2, 1) AND Date() as the criteria
under the [Booked_Date] field.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Hello,

I have a table with dates [Booked_Date] ranging from Oct
1,2003 to present (12/9/2003). I'm trying to create a
query that will pull in all dates from previous months
(Oct and Nov)dynamically. I've tried various syntax
expressions with no luck.

Attempted Example:

Format:([Booked_Date],"m") - 1

And unfortunatley, this does not work as well. Does
anyone have any suggestions?

Thanks.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top