Query by current date using month & year

[Guest]

I am trying to set up a query in Frontpage Wizard custom query to return
field values in my
database based on current date base on year and month only using format of
mm/dd/yyy where dd will be a wild card value (I think?) have tried several
options such as

WHERE (PN = ':N::' AND Period = '" & MonthName(Month(Date)) & " " &
Year(Date) & "')

WHERE (PN = ':N::' AND Period = '" & Monthname(Date) & "/01/" & Year(Date)
& "')

WHERE (PN = ':N::' AND Period = '" & (Month(Date) & "1" & Year(Date) & "')

WHERE (PN = ':N::' AND Period = '" & (Month(Date) & "/01/" & Year(Date) &
"')

PN is another field

But no luck

If anyone has any ideas it would be greatly appreciated

trying to avoid using the defalt date as we would then have a lot of pages
to update each month

Thank you

 

[Douglas J. Steele]

Exactly what is stored in Period (and what data type is it)?

Today is 27 Oct, 2007. Here's what each of your attempts is going to give
you:

MonthName(Month(Date)) & " " & Year(Date) ==> October 2007
MonthName(Date) & "/01/" & Year(Date) ==> Error
Month(Date) & 1 & Year(Date) ==>1012007
Month(Date) & "/01/" & Year(Date) ==> 10/01/2007

If Period is a text field, then what you return above has to be exactly
what's stored in the field.

If Period is a date field, then what's actually stored in it? Dates have to
be complete dates: you can't just store a month and year in a date field.

 

[Guest]

With a great deal of help using current server date as query date has now
been achieved and we can get results. However we really need to use the
current date as our defalt date instead of inserting a current date into the
Frontpage DBRW reason is we are setting up lots of pages rather than one page
for editing by individual clients

Following are our field values and queries, our objective is to access
results or editor and have the default (current month) show up as the default
result then be able to search for different months

Fields are

PN - a numeric value that is put into the query by either hyperlink or
default in Frontpage Database Wizard

Period is a month represented by for example 10/1/2007 being October 1 2007

Current date SQL is either (equal) Month(Period)="" & Month(Date()) & "" AND
Year(Period)= "" & Year(Date()) (or greater than) Month(Period) > "" &
Month(Date()) & "" AND Year(Period)= "" & Year(Date())


Queries are

(PN = :N:: AND Period = ':eriod::')

(PN = :N:: AND (Period = ':eriod::' OR Period > ':eriod::'))

(Period > ':eriod::' AND PN = ':N::')

(Period = ':eriod::' AND PN = ':N::')

Any help in showing us how to include the current month as a default in
these queries would be greatly appreciated thank you, we have tried several
ways but get either weird or no result.

 

[Douglas J. Steele]

I'm sorry, you really haven't told me what Period is. Is it a date field or
a text field?

 

[Guest]

Sorry Douglas Period is a date field, PN is a numeric field

Thank you

 

[Douglas J. Steele]

If Period is a date field, then you must pass it a date. October 2007 isn't
a date.

If you want every record for the current month, try something like

WHERE Period BETWEEN DateSerial(Year(Date), Month(Date), 0) AND
DateSerial(Year(Date), Month(Date) + 1, 0)

If Period actually contains a time as well as a date, use

WHERE Period BETWEEN DateSerial(Year(Date), Month(Date), 0) AND
DateSerial(Year(Date), Month(Date) + 1, 1)