Re: Append Query Date Criteria Problem.

Temporarily remove the line that sets warnings to False.
Then read the dialog boxes.

Does it report an error? Or does it say it's appending 0 records?

If there is a message about violating rules or not being able to append, this might help you to track down the issue:
Why can't I append some records?
at:
http://allenbrowne.com/casu-19.html

If it says it's appending 0 records, we need to work on why it isn't selecting the right records.

Another way to get information about the error is to use the Execute method, and read the error message:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.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:1176010182.691852.234860@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Apr 7, 10:04 pm, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx> wrote:
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

"Hunter57" <Pwoo...@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- Hide quoted text -

- Show quoted text -

Hi Allen,

Thanks very much for your help. However, I tried it all and then some
and still could not append to my table. I have removed the date from
my query and I am planning to add the date using something like this:

Private Sub btnAddDate_Click()
On Error GoTo Err_btnAddDate_Click

Do While Not EOF
If (IsNull(Me.SSClassDate) And (Me.Attendance = 0)) Then
Me.SSClassDate.SetFocus
Me.SSClassDate = Me.txtSSClassDate
DoCmd.FindNext
Loop

Else: GoTo Exit_btnAddDate_Click
End If


Exit_btnAddDate_Click:
Exit Sub

Err_btnAddDate_Click:
MsgBox Err.Description
Resume Exit_btnAddDate_Click

End Sub

I have to work on my VBA so I can make the code work. But it is way
past my bedtime so I will leave it for another day.

.