Re: Append Query Date Criteria Problem.

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

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

--
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" <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.

With Gratitude,

Hunter57

.

Quantcast