openform using criteria to link by date...

  • Thread starter Thread starter ucdcrush
  • Start date Start date
U

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.
 
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?
 
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
 
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\#" )
 
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.
 
Back
Top