Default Date Entry

B

bdehning

I use the following in many queries to have users search for records.
[Enter Beginning Date: 00/00/00]) Is there a way to have the / / done
automatically so the user only enters numbers like 020505 and yet the date
prompt works?

Brian
 
K

Ken Snell [MVP]

Assuming that your users are entering the date using a mmddyy format:

SELECT * FROM MyTable
WHERE MyDate =
DateSerial(Right([Enter Beginning Date: 000000], 2),
Left([Enter Beginning Date: 000000], 2),
Mid([Enter Beginning Date: 000000], 3, 2));
 
B

bdehning

Ken How do I apply what you said to the following. I keep getting errors!

SELECT [ASSIGNED CONSULTANT],[DATE WRITTEN REPORT SENT],[INITIAL
EVALUATION],[PROGRAM/MGMT EVALUATION],[JOBSITE SURVEY],[OPERATIONS
SURVEY],[LOSS ANALYSIS],[ACCIDENT INVESTIGATION],[TRAINING],[SAFETY
MEETING],[ACTION PLANNING],[PROGRESS REPORT],[INDUSTRIAL
HYGIENE/ERGONOMICS],[PHONE SURVEY/OTHER],[LOCATION SERVICING DIVISION],[CALL
TYPE],[TYPE OF SERVICE],[STAFF
MEETING/TRAINING],[VACATION/SICK/HOLIDAY],[SPECIAL
PROJECTS],[FIRSTOFNOTEWORTHY ACCOMPLISHMENTS FOR THE MONTH],[MONTHLY REPORT
DATE ENTERED],[FIRSTOFCOMMENTS]
FROM[Service Combined]
WHERE (([Assigned Consultant] Like [Enter Consultant Name] & "*")and [Date
Written Report Sent]>=[Enter Beginning Date: 00/00/00])


UNION ALL SELECT [ASSIGNED CONSULTANT],[Date Written Report sent],[INITIAL
EVALUATION],[PROGRAM/MGMT EVALUATION],[JOBSITE SURVEY],[OPERATIONS
SURVEY],[LOSS ANALYSIS],[ACCIDENT INVESTIGATION],[TRAINING],[SAFETY
MEETING],[ACTION PLANNING],[PROGRESS REPORT],[INDUSTRIAL
HYGIENE/ERGONOMICS],[PHONE SURVEY/OTHER],[LOCATION SERVICING DIVISION],[CALL
TYPE],[TYPE OF SERVICE],[STAFF
MEETING/TRAINING],[VACATION/SICK/HOLIDAY],[SPECIAL
PROJECTS],[FIRSTOFNOTEWORTHY ACCOMPLISHMENTS FOR THE MONTH],[MONTHLY REPORT
DATE ENTERED],[FIRSTOFCOMMENTS]
FROM[Report Completion Input Request]
WHERE (([Assigned Consultant] Like [Enter Consultant Name] & "*") and[Date
Written Report Sent]>= [Enter Beginning Date: 00/00/00]);


Ken Snell said:
Assuming that your users are entering the date using a mmddyy format:

SELECT * FROM MyTable
WHERE MyDate =
DateSerial(Right([Enter Beginning Date: 000000], 2),
Left([Enter Beginning Date: 000000], 2),
Mid([Enter Beginning Date: 000000], 3, 2));
--

Ken Snell
<MS ACCESS MVP>




bdehning said:
I use the following in many queries to have users search for records.
[Enter Beginning Date: 00/00/00]) Is there a way to have the / / done
automatically so the user only enters numbers like 020505 and yet the date
prompt works?

Brian
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Change this line (in both queries)
WHERE (([Assigned Consultant] Like [Enter Consultant Name] & "*")and
[Date
Written Report Sent]>=[Enter Beginning Date: 00/00/00])


to this line (in both queries)

WHERE (([Assigned Consultant] Like [Enter Consultant Name] & "*")and [Date
Written Report Sent]>=DateSerial(Right([Enter Beginning Date: 000000], 2),
Left([Enter Beginning Date: 000000], 2),
Mid([Enter Beginning Date: 000000], 3, 2)))


--

Ken Snell
<MS ACCESS MVP>

bdehning said:
Ken How do I apply what you said to the following. I keep getting errors!

SELECT [ASSIGNED CONSULTANT],[DATE WRITTEN REPORT SENT],[INITIAL
EVALUATION],[PROGRAM/MGMT EVALUATION],[JOBSITE SURVEY],[OPERATIONS
SURVEY],[LOSS ANALYSIS],[ACCIDENT INVESTIGATION],[TRAINING],[SAFETY
MEETING],[ACTION PLANNING],[PROGRESS REPORT],[INDUSTRIAL
HYGIENE/ERGONOMICS],[PHONE SURVEY/OTHER],[LOCATION SERVICING
DIVISION],[CALL
TYPE],[TYPE OF SERVICE],[STAFF
MEETING/TRAINING],[VACATION/SICK/HOLIDAY],[SPECIAL
PROJECTS],[FIRSTOFNOTEWORTHY ACCOMPLISHMENTS FOR THE MONTH],[MONTHLY
REPORT
DATE ENTERED],[FIRSTOFCOMMENTS]
FROM[Service Combined]
WHERE (([Assigned Consultant] Like [Enter Consultant Name] & "*")and
[Date
Written Report Sent]>=[Enter Beginning Date: 00/00/00])


UNION ALL SELECT [ASSIGNED CONSULTANT],[Date Written Report sent],[INITIAL
EVALUATION],[PROGRAM/MGMT EVALUATION],[JOBSITE SURVEY],[OPERATIONS
SURVEY],[LOSS ANALYSIS],[ACCIDENT INVESTIGATION],[TRAINING],[SAFETY
MEETING],[ACTION PLANNING],[PROGRESS REPORT],[INDUSTRIAL
HYGIENE/ERGONOMICS],[PHONE SURVEY/OTHER],[LOCATION SERVICING
DIVISION],[CALL
TYPE],[TYPE OF SERVICE],[STAFF
MEETING/TRAINING],[VACATION/SICK/HOLIDAY],[SPECIAL
PROJECTS],[FIRSTOFNOTEWORTHY ACCOMPLISHMENTS FOR THE MONTH],[MONTHLY
REPORT
DATE ENTERED],[FIRSTOFCOMMENTS]
FROM[Report Completion Input Request]
WHERE (([Assigned Consultant] Like [Enter Consultant Name] & "*")
and[Date
Written Report Sent]>= [Enter Beginning Date: 00/00/00]);


Ken Snell said:
Assuming that your users are entering the date using a mmddyy format:

SELECT * FROM MyTable
WHERE MyDate =
DateSerial(Right([Enter Beginning Date: 000000], 2),
Left([Enter Beginning Date: 000000], 2),
Mid([Enter Beginning Date: 000000], 3, 2));
--

Ken Snell
<MS ACCESS MVP>




bdehning said:
I use the following in many queries to have users search for records.
[Enter Beginning Date: 00/00/00]) Is there a way to have the / /
done
automatically so the user only enters numbers like 020505 and yet the
date
prompt works?

Brian
Click to expand...
Click to expand...
Click to expand...
 
B

bdehning

Thanks Ken, I was making it too hard. It works great!!

Ken Snell said:
Change this line (in both queries)
WHERE (([Assigned Consultant] Like [Enter Consultant Name] & "*")and
[Date
Written Report Sent]>=[Enter Beginning Date: 00/00/00])


to this line (in both queries)

WHERE (([Assigned Consultant] Like [Enter Consultant Name] & "*")and [Date
Written Report Sent]>=DateSerial(Right([Enter Beginning Date: 000000], 2),
Left([Enter Beginning Date: 000000], 2),
Mid([Enter Beginning Date: 000000], 3, 2)))


--

Ken Snell
<MS ACCESS MVP>

bdehning said:
Ken How do I apply what you said to the following. I keep getting errors!

SELECT [ASSIGNED CONSULTANT],[DATE WRITTEN REPORT SENT],[INITIAL
EVALUATION],[PROGRAM/MGMT EVALUATION],[JOBSITE SURVEY],[OPERATIONS
SURVEY],[LOSS ANALYSIS],[ACCIDENT INVESTIGATION],[TRAINING],[SAFETY
MEETING],[ACTION PLANNING],[PROGRESS REPORT],[INDUSTRIAL
HYGIENE/ERGONOMICS],[PHONE SURVEY/OTHER],[LOCATION SERVICING
DIVISION],[CALL
TYPE],[TYPE OF SERVICE],[STAFF
MEETING/TRAINING],[VACATION/SICK/HOLIDAY],[SPECIAL
PROJECTS],[FIRSTOFNOTEWORTHY ACCOMPLISHMENTS FOR THE MONTH],[MONTHLY
REPORT
DATE ENTERED],[FIRSTOFCOMMENTS]
FROM[Service Combined]
WHERE (([Assigned Consultant] Like [Enter Consultant Name] & "*")and
[Date
Written Report Sent]>=[Enter Beginning Date: 00/00/00])


UNION ALL SELECT [ASSIGNED CONSULTANT],[Date Written Report sent],[INITIAL
EVALUATION],[PROGRAM/MGMT EVALUATION],[JOBSITE SURVEY],[OPERATIONS
SURVEY],[LOSS ANALYSIS],[ACCIDENT INVESTIGATION],[TRAINING],[SAFETY
MEETING],[ACTION PLANNING],[PROGRESS REPORT],[INDUSTRIAL
HYGIENE/ERGONOMICS],[PHONE SURVEY/OTHER],[LOCATION SERVICING
DIVISION],[CALL
TYPE],[TYPE OF SERVICE],[STAFF
MEETING/TRAINING],[VACATION/SICK/HOLIDAY],[SPECIAL
PROJECTS],[FIRSTOFNOTEWORTHY ACCOMPLISHMENTS FOR THE MONTH],[MONTHLY
REPORT
DATE ENTERED],[FIRSTOFCOMMENTS]
FROM[Report Completion Input Request]
WHERE (([Assigned Consultant] Like [Enter Consultant Name] & "*")
and[Date
Written Report Sent]>= [Enter Beginning Date: 00/00/00]);


Ken Snell said:
Assuming that your users are entering the date using a mmddyy format:

SELECT * FROM MyTable
WHERE MyDate =
DateSerial(Right([Enter Beginning Date: 000000], 2),
Left([Enter Beginning Date: 000000], 2),
Mid([Enter Beginning Date: 000000], 3, 2));
--

Ken Snell
<MS ACCESS MVP>




I use the following in many queries to have users search for records.
[Enter Beginning Date: 00/00/00]) Is there a way to have the / /
done
automatically so the user only enters numbers like 020505 and yet the
date
prompt works?

Brian
Click to expand...
Click to expand...
Click to expand...
 
Top