Re: Append Query Date Criteria Problem.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

If the append query is misunderstanding the date, there are several levels to check.

1. Open table tblSSAttendance in design view.
Verify that the SSClassDate field is of type Date/Time (not Text.)
Verify that SSClassID is a Number field, of size Long Integer.
The follow steps assume these settings.

2. Open form frmSSAttendEntry in design view.
If txtSSClassDate is unbound, set its Format property to:
General Date
so Access knows it is a date.
If cboSSClassID is unbound, set its Format property to:
General Number

3. Open the query in design view.
Choose Parameters on the Query menu.
Access opens the paramters dialog.
Enter 2 rows:
[Forms]![frmSSAttendEntry]![txtSSClassDate] Date
[Forms].[frmSSAttendEntry].[cboSSClassID] Long

The unbound text box should now be read as a date, and the query should now perform a date match.

More information on getting Access to recognise dates:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Hunter57" <Pwood57@xxxxxxxxx> wrote in message
news:1175993363.061068.78560@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I need some help with an append query. The date criteria is not
working in the append query, but it works fine when I change it to a
Select query. Also, I can remove the problem Column in the RAD, or
just remove the critera in the column and the append query works fine.

I am using a continuous form to enter Sunday School attendance. I
want my query to add all the information for a particular class on a
particular day and the user just has to enter the Attendance. My
query has two criteria, which are obtained from unbound controls on
the form: cboSSClassID, and txtSSClassDate (the one giving me the
problem). All of my table fields and form controls for this field are
set to use this input mask: 99/99/0000;0;_.

I use a command button to open the query with the following code:

Private Sub btnAddNewRecords_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "QASSAttendRec"
DoCmd.SetWarnings True
End Sub

Here is my Query SQL:
INSERT INTO tblSSAttendance ( SSClassDate, SSClassID, SSMemberID,
MemberID, ContactID )
SELECT tblSSAttendance.SSClassDate, tblSSAttendance.SSClassID,
tblSSAttendance.SSMemberID, tblSSAttendance.MemberID,
tblSSAttendance.ContactID
FROM tblSSClasses INNER JOIN tblSSAttendance ON tblSSClasses.SSClassID
= tblSSAttendance.SSClassID
WHERE (((tblSSAttendance.SSClassDate)=[Forms]![frmSSAttendEntry]!
[txtSSClassDate]) AND ((tblSSAttendance.SSClassID)=[Forms].
[frmSSAttendEntry].[cboSSClassID]));

I have two other command buttons which filter by query: one to show
the records just added, and another to show all data in the table.
They use the exact same date criteria and work well.

I am using MS Access 2003 in 2000 format with Windows XP.

I would appreciate your help.

Hunter57

.

Quantcast