date/time

[Guest]

My problem is I would like to open a form where the result of text box which is in format dd/mm/yyy hh:mm am/pm matches the date in another field in separate form which also has format dd/mm/yyy hh:mm am/pm but want to show all of same date regardless of time .I would like to ignore time element of field
can I modify following to do this? or how do I do it
Private Sub Command38_Click(
On Error GoTo Err_Command38_Clic

Dim stDocName As Strin
Dim stLinkCriteria As Strin

stDocName = "CHECK DATE

stLinkCriteria = "[BookTime]=" & "#" & Me![TempBookTime] & "#
DoCmd.OpenForm stDocName, , , stLinkCriteri

Exit_Command38_Click
Exit Su

Err_Command38_Click
MsgBox Err.Descriptio
Resume Exit_Command38_Clic

End Su
hope this makes sense
Thanks

 

[Allen Browne]

To include all the times from that date, ask for everything from the start
of the day to less than the next day:

stLinkCriteria = "([BookTime] >= " & _
Format(Me![TempBookTime], "\#mm\/dd\/yyyy\#") & _
") And ([BookTime] < " & _
Format(Me![TempBookTime] + 1, "\#mm\/dd\/yyyy\#") & ")"

Please note that because you are working in a dd/mm/yyyy format, it is
crucial to format the literal dates within the SQL string in the American
format. More information in article:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My problem is I would like to open a form where the result of text box

which is in format dd/mm/yyy hh:mm am/pm matches the date in another field
in separate form which also has format dd/mm/yyy hh:mm am/pm but want to
show all of same date regardless of time .I would like to ignore time
element of fields

can I modify following to do this? or how do I do it?
Private Sub Command38_Click()
On Error GoTo Err_Command38_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CHECK DATE"

stLinkCriteria = "[BookTime]=" & "#" & Me![TempBookTime] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub
hope this makes sense
Thanks

 

[Nikos Yannacopoulos]

Tina,

Try this:
stLinkCriteria = "[BookTime]=" & "#" & Int(Me![TempBookTime]) & "#"

If it doesn't work right off then your date formats are probably not US
(MDY), which is the only format VBA understands. In that case, you'll have t
use a Format function on the date parameter to make sure you pass it on in
the required format.

HTH,
Nikos

My problem is I would like to open a form where the result of text box

which is in format dd/mm/yyy hh:mm am/pm matches the date in another field
in separate form which also has format dd/mm/yyy hh:mm am/pm but want to
show all of same date regardless of time .I would like to ignore time
element of fields

can I modify following to do this? or how do I do it?
Private Sub Command38_Click()
On Error GoTo Err_Command38_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CHECK DATE"

stLinkCriteria = "[BookTime]=" & "#" & Me![TempBookTime] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub
hope this makes sense
Thanks

 

[Tim Ferguson]

My problem is I would like to open a form where the result of text box
which is in format dd/mm/yyy hh:mm am/pm

That's a horrid thing to make them type: still, never mind.

but want to show all of same date regardless of time .I would like to

stLinkCriteria = "[BookTime]=" & "#" & Me![TempBookTime] & "#"

You need two more things: the easy one is the DateValue function, and the
important one is how Jet and Access do dates.

Jet SQL expects all dates in a limited number of formats, and dd/mm/yyyy is
not one of them. Acceptable formats are the international ISO yyyy-mm-dd
and the USian mm/dd/yyyy -- and you already know about the # delimiters.
Access on the other hand does not give a stuff about Jet, and will handle
dates according to the regional settings in the control panel. These two
facts give rise to a number of bugs, but the bottom line for the developer
is _always_ to take control of how dates are passed to Jet.

FWIW, I would recast the criterion into something like this:

stLinkCriterion = "DATEVALUE([BookTime]) = " & _
Format$(Me!TempBookTime, "\#yyyy\-mm\-dd\#)

which is guaranteed to work regardless of the language or regional
settings.

Hope that helps


Tim F