openform using criteria to link by date...

[ucdcrush]

Hi everyone!

I am having a problem opening a form and specifying date as link
criteria. My table has these fields:

childId
crDate

I have a form which uses a combo box where users select the child ID #
and the date of the chart review (on the same row). They should then be
able to open a form for that specific chart review date and patient ID.

The button they click has the following code. None of the
stLinkCriteria I specify seems to work. The 2nd stLinkCriteria which is
commented out is the one I would like to end up with (link by date and
patient ID) but I am not even able to get the date-only criteria
working. The error simply says "Runtime error 2501 - the openform
action was cancelled" and it takes me to the Docmd.Openform line.

--------------------------
Private Sub buttonEditCr_Click()
Dim stDocName As String
Dim stLinkCriteria As String
clientList.SetFocus

basGlobal.chartReview = "1"

stDocName = "frmCr"

stLinkCriteria = "[crDate]=" & "'" & basGlobal.clientDate & "'"
'stLinkCriteria = "[childID]=" & "'" & basGlobal.clientID & "' AND
[crDate] = '" & basGlobal.clientDate & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
--------------------------

basGlobal.clientDate is defined as a global variable of type date, and
it contains (verified by a msgbox that shows its value) the correct
date, I just cannot seem to get it to link up to the table (which has
crDate as a date with format short date).

By the way, the code works fine if I link by only the clientID, but
then it does not necessarily show the correct chart review (wrong date)
for that patient.

Any ideas how to get this working?

Thanks.

 

[ucdcrush]

DOH! Got it, should not have had the ' ' around the date...

 

[ucdcrush]

Spoke too soon.. it does not give an error, but it does not find the
date in the table either. Should I be converting it?

 

[Tom Ellison]

Dear Rush:

The format has nothing to do with anything except how the data is displayed.

Could it be that you have a time component in some or all the values in the
table? If so, you would need to strip it off before making comparisons.
3/15/2006 is not equal to 3/15/2006 at 14:25:36.

Tom Ellison

 

[John Spencer]

Dates should be delimited with # signs in Access and should be in MM/DD/YYYY
format or in YYYY/MM/DD format.

stLinkCriteria = "[crDate]=" & "#" & basGlobal.clientDate & "#"

Or try
stLinkCriteria = "[crDate]=" &
Format(basGlobal.clientDate,"\#yyyy/mm/dd\#" )

 

[ucdcrush]

THANK YOU JOHN!! Works great

Thanks Tom also for the reply.. I had a feeling I was not representing
it in the stLinkCriteria string correctly.