Date Field Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In one Form - "Dates" I have the Fields - "Start Date" and "End Date" where
the user will input the date ranges of the data he wants to retrieve.

In another Form - "Main Form" I have a Field - "Date".

When the user enters a "Start Date" and "End Date" form the "Dates" form and
then presses a command button I want the "Main Form" to open with records
between those dates. How do I code this?

Thanks in advance.
 
My first piece of advice would be to rename your field. Date is a reserved
word, and shouldn't be used for your own purposes. Try WhatDate or MyDate or
something like that.

In your button's Click event, put something like:

DoCmd.OpenForm "Main Form", acNormal, , _
"MyDate Between " & _
Format(Me.[Start Date], "\#mm\/dd\/yyyy\#") & _
" And " & _
Format(Me.[End Date], "\#mm\/dd\/yyyy\#")
 
Playa said:
In one Form - "Dates" I have the Fields - "Start Date" and "End Date" where
the user will input the date ranges of the data he wants to retrieve.

In another Form - "Main Form" I have a Field - "Date".

When the user enters a "Start Date" and "End Date" form the "Dates" form and
then presses a command button I want the "Main Form" to open with records
between those dates. How do I code this?
Click to expand...


The command button's Click event procedure would look
something like this untested air code:

Dim stDoc As String
Dim stCriteria As String

stDoc = "Main Form"
stCritria = "[Date] Between " & _
Format([Start Date], "\#m\/d\/yyyy\#") & " And " & _
Format([End Date], "\#m\/d\/yyyy\#")
DoCmd.OpenForm stDoc, , , stCriteria
 
In one Form - "Dates" I have the Fields - "Start Date" and "End Date" where
the user will input the date ranges of the data he wants to retrieve.

In another Form - "Main Form" I have a Field - "Date".

When the user enters a "Start Date" and "End Date" form the "Dates" form and
then presses a command button I want the "Main Form" to open with records
between those dates. How do I code this?

Thanks in advance.
Click to expand...

DoCmd.OpenForm "MainForm", , , "[DateField] Between #" & [Start Date]
& "# and #" & [End Date] & "#"

By the way, if you really do have a field named "Date" I would suggest
you change it to some other word, perhase SalesDate, dteDate, ADate,
etc.

Date, as well as many other words, is a reserved Access/VBA/Jet word
and should not be used as a field name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
 
Back
Top