UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need To Sort Student Attendance By Date, Access 2007    
 
   
philr
post Dec 22 2019, 06:41 AM
Post#1



Posts: 2
Joined: 22-December 19



In advance I would like to thank all who can help me with this problem.

I have a table with the following fields: StudentID, AttendDate, FirstName, LastName with the following data types StudentID = number, AttendDate = Date/Time, FirstName = Text, LastName=Text

I created a combo box (named cboAttendDate) of the dates in the table using the following code: SELECT DISTINCT [tblAttend].[AttendDate] FROM tblAttend ORDER BY [AttendDate] DESC;

From the combo box I would select a date and then the students who attended on that date would show up in a table or subform.

I've entered the following code for the combo box but nothing is happening:

Private Sub cboAttendDate_AfterUpdate()
Dim dateAttend As Date
myStudent = "Select * from tblAttend where ([dateAttend] = " & Me.cboAttendDate & ")"
Me.tblAttend_subform1.Form.RecordSource = myStudent
Me.tblAttend_subform1.Form.Requery

End Sub


It has been 20+ years since I've programmed any Access databases (I've been using php and MySql since then for web design and databases, so yes my knowledge of access has slipped away )

I hope that someone can either provide me with the code and directions that I need or email me a link to a tutorial that covers this situation. I've found one tutorial but it does not provide the DISTINCT aspect and I have to click on each repetitive date to see the results.

Thanks
Phil
Go to the top of the page
 
cheekybuddha
post Dec 22 2019, 07:34 AM
Post#2


UtterAccess Moderator
Posts: 12,112
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

Dates must be delimited with octothorpes ('#') when passed as criteria to SQL in Access.

Additionally, they must be passed in 'unambiguous' format irrespective of how you format dates locally - this equates to either ISO date format (yyyy-mm-dd hh:nn:ss) or US date format (mm/dd/yyyy hh:nn:ss). If you are working in English you can also use dd/mmm/yyyy hh:nn:ss.

If you are not dealing with times then you can omit the hh:nn:ss

So, adapt your code:
CODE
Private Sub cboAttendDate_AfterUpdate()

  Dim dateAttend As Date
  
  myStudent = "Select * from tblAttend where ([dateAttend] = " & Format(Me.cboAttendDate, "\#yyyy\-mm\-dd hh:nn:ss\#") & ")"
  Me.tblAttend_subform1.Form.RecordSource = myStudent

End Sub

No need to requery when setting RecordSource - it happens implicitly.

hth,

d


--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Dec 22 2019, 08:08 AM
Post#3


UtterAccess Moderator
Posts: 12,112
Joined: 6-December 03
From: Telegraph Hill


BTW, cboAttendDate should be unbound (ie empty ControlSource)

--------------------


Regards,

David Marten
Go to the top of the page
 
philr
post Dec 23 2019, 10:36 AM
Post#4



Posts: 2
Joined: 22-December 19



Thanks Dave, This works beautifully. Now I can move forward with this DB.

Question though.

In both the code I send and you wrote there were two lines.

myStudent = "Select * from tblAttend where ([dateAttend] = " & Format(Me.cboAttendDate, "\#yyyy\-mm\-dd hh:nn:ss\#") & ")"
Me.tblAttend_subform1.Form.RecordSource = myStudent

Is it possible to write the code as a single line? Like this instead...

Me.tblAttend_subform1.Form.RecordSource = "Select * from tblAttend where ([dateAttend] = " & Format(Me.cboAttendDate, "\#yyyy\-mm\-dd hh:nn:ss\#") & ")"
Go to the top of the page
 
isladogs
post Dec 23 2019, 10:48 AM
Post#5


UtterAccess VIP
Posts: 2,109
Joined: 4-June 18
From: Somerset, UK


PMFJI.
Yes you can do this on one line if you prefer.

Also since DateAttend (or is it AttendDate) is a field in your table, the Dim line isn't required

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th January 2020 - 12:51 AM