We help IT Professionals succeed at work.

Date criteria in Querydef

tluce
tluce asked
on
I have got a table containing names and addresses, and I need to occasionally print a page of labels for individual people on the table.  If you enter the person's full name on a form, it may select more than one record (in other words, there may be genuinely more than one person with that name). In order to select the correct record, I then ask for the date of birth (in short date format), then open the querydef in VBA, and alter the SQL property to include the date on the end. When a recordset is used to pick up the record, it sometimes gives the error 'No Current Record'. It does this if the month you enter is between January and September, i.e. one-digit, and it still does it even if you type in a leading zero. If you go to query design, it will have a date in the format dd/m/yy, where it has been automatically 'truncated' and gives no records - as soon as you put a leading zero in manually it will select the correct record. I have tried various things including: change the date format, put an input mask on the form control, various things with the Mid() function... Any ideas?

Comment
Watch Question

Commented:
I would add an autonumber to the persons table and apply that (after e.g. the DOB selection) to print the specified person.
Personally I always show the users a form with a datasheet subform where they can select the wanted row by the mouse pointer and/or filter/sort the data on e.g. name and press a button to print the wanted person.
Drop me a line at my nico5038 mailbox "at" yahoo.com and I'll mail a sample.

Nic;o)

Commented:
always convert the date column for comparison.
CDATE(BirthDate) and this will force it to be a date value.
Try it else send me the mdb (97 only) at devthaa@yahoo.com
Commented:
Hi,
SQL statement is just a string, where date value must be in American format (mm/dd/yy) enclosed into #, so when you get a string (date value)from textbox in the form and append it to your querydef you should to do all things mentioned above:
strSQL := strSQL & " AND fldBerthDate = #" & Format(CDate(me.txtBerthDate), "mm/dd/yy") & "#;"

Hope it helps,
Dedushka

Author

Commented:
I have tried out the CDate examples, and it still writes incorrectly to the QueryDef.

Commented:
Dates are always a problem, that's why I proposed to add an autonumber and to show the names/dob's in a (datasheet) subform.
Makes it more userfriendly and you don't need this date trouble to be solved...

Nic;o)

Commented:
tluce??

Author

Commented:
I managed to get it working by not using the Format function - the date is a string so putting it in mm/dd/yy format works.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.