Results 1 to 13 of 13
  1. #1
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45

    Drop down Menu containing dates

    Hello All:



    I'm a real newcomer to Access and self taught - I took on the project of converting our excel spreadsheet to an Access Database for our school/student database and boy have I created a monster.

    My question of the day is as follows:

    All students must write and pass a Provincial Exam in order to get licensed. When a student registers for a course they are scheduled for the next available exam.
    I can generate a proper report if I input the date directly into the query -- example #5/19/16#. This will produce a report for all students writing the exam on 19 May 2016.

    I have created a drop down menu that contains all possible exam dates.

    I have attempted to create a query criteria - [Forms]![FormName]![FieldName]

    Now I have been able to make this concept work in many other situations but can't seem to figure it out when the date is involved.
    Am I missing something?

    Any assistance would be greatly appreciated.
    Thanks,
    Rick

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What is the form name and the control name?
    What is the SQL of the query?

  3. #3
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    SQL =

    SELECT Student.StudentID, Student.LastName, Student.FirstNames, Student.Address, Student.City, Student.Prov, Student.PostalCode, Student.HomePhone, Student.CellPhone, Student.DateofBirth, Exam.Attempt, Exam.ExamDate, Exam.ExamTime, Exam.Status

    FROM Student INNER JOIN Exam ON Student.StudentID = Exam.StudentID

    WHERE (((Exam.ExamDate)=#5/19/2016#))

    ORDER BY Student.LastName, Student.FirstNames;


    In the query if put - #5/19/2016# -- This gives me the desired result

    Form Name = ProvExamRoster : Form
    Unbound Combo Box = cboSerial
    SQL =
    SELECT Student.StudentID, Student.LastName, Student.FirstNames, Student.Address, Student.City, Student.Prov, Student.PostalCode, Student.HomePhone, Student.CellPhone, Student.DateofBirth, Exam.Attempt, Exam.ExamDate, Exam.ExamTime, Exam.Status

    FROM Student INNER JOIN Exam ON Student.StudentID = Exam.StudentID

    WHERE (((Exam.ExamDate)=[Forms]![ProvExamRoster : Form]![cboSerial]))

    ORDER BY Student.LastName, Student.FirstNames;

    In the query if put - [Forms]![ProvExamRoster : Form]![cboSerial] -- This gives me a blank query

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your issue is that you have not built/concatenated the sql string properly. Rather than repeat from one or two days ago, take a look at this thread on concatenating with control names. I gave a detailed explanation on the subject, but there's other good stuff there so you should follow it through.

    https://www.accessforums.net/showthread.php?t=59793
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    WHERE (((Exam.ExamDate)=[Forms]![ProvExamRoster : Form]![cboSerial]))
    Do you really have a form named "ProvExamRoster : Form" ???? This is a poor name. Object names should only be letters and numbers (exception is the underscore).
    NO spaces, punctuation or special characters.

    You say the query works. You manually enter the date in the criteria row of the query and records are returned.
    So the problem is the form name or the value in the combo box.

    What is the SQL of the combo box ROW source?
    What is the Bound column?
    What is the Column Count?


    Or post the dB for analysis......

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Definitely a bad name. But, it's enclosed in [ ] so it should work. The problem as I see it, is that what's in the control looks like a date, but it isn't as far as Jet is concerned.
    This part of the sql statement
    (((Exam.ExamDate)=[Forms]![ProvExamRoster : Form]![cboSerial])) has to be
    "Exam.ExamDate= #" & [Forms]![ProvExamRoster : Form]![cboSerial]) & "#"

    or at least a variation that accomplishes the same thing within the rest of the sql statement.

  7. #7
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    I changed the name of the form to -- frmProvExamRoster

    SQL of combo box (cboSerial) Row Source

    SELECT Serial.SerialID, Serial.ExamDate
    FROM Serial
    WHERE (((Serial.CourseID)=7))
    ORDER BY IIf(([StartDate]-Date())+30>=0,(100000-([StartDate]-Date())-30),(10000-([StartDate]-Date())-30)) DESC;

    Bound Column is 1 (Serial.ExamDate)
    Column Count is 2
    Column Widths are 0";2.1146"

  8. #8
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    I try the following

    WHERE "Exam.ExamDate=#" & [Forms]![frmProvExamRoster]![cboSerial] & "#"

    The design view gives me a new Field ---- "Exam.ExamDate=#" & [Forms]![frmProvExamRoster]![cboSerial] & "#"
    as well with the criteria of <>False

    My old field of ExamDate is still there as well.

    ALSO - In Datasheet view i get a Field Name of "Expr1014" and data in the field is ---- Exam.ExamDate=##

    Maybe Im just too dumb to grasp this concept.


    Quote Originally Posted by Micron View Post
    Definitely a bad name. But, it's enclosed in [ ] so it should work. The problem as I see it, is that what's in the control looks like a date, but it isn't as far as Jet is concerned.
    This part of the sql statement
    (((Exam.ExamDate)=[Forms]![ProvExamRoster : Form]![cboSerial])) has to be
    "Exam.ExamDate= #" & [Forms]![ProvExamRoster : Form]![cboSerial]) & "#"

    or at least a variation that accomplishes the same thing within the rest of the sql statement.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First, I presumed you were running this sql in code, so I may have mis-directed you. Sorry if that's the case, but you can use the info anyway for the day you get around to doing that. Keep in mind that I expected you would read enough of the thread link I posted to grasp the idea. I did not consider the whole context of your sql statement, only part of it. I left it to you to try wrapping anything that needed to be delimited, but I see that you left the WHERE clause (and probably much of the rest of it) outside of the quotes. So that info relates to building sql in code, which you might want to keep on the back burner for now.

    Based on your post #7, I have a different take on the issue.
    Bound Column is 1 (Serial.ExamDate)
    If the combo box bound column is 1 as you posted, the value being returned is Serial.SerialID not a date.
    Serial.SerialID Serial.ExamDate
    column 1 column 2

    Likely you are aware that a combo box column order is zero based, but 0 is the list index which is never visible. So I think you chose 1, thinking it is the second column after zero. This would also explain the lack of data - your date field is being passed the SerialID. Either re-arrange your field order by reversing the fields in the query OR change your reference to column(2) OR change the bound column to 2 (likely the easiest). If you specify the column in the sql, it would be like this, I think:
    Code:
    SELECT Student.StudentID, Student.LastName, Student.FirstNames, Student.Address, Student.City, Student.Prov, Student.PostalCode, 
    Student.HomePhone, Student.CellPhone, Student.DateofBirth, Exam.Attempt, Exam.ExamDate, Exam.ExamTime, Exam.Status FROM 
    Student INNER JOIN Exam ON Student.StudentID = Exam.StudentID WHERE (((Exam.ExamDate)=Forms!frmProvExamRoster.cboSerial.Column(2)))
    ORDER BY Student.LastName, Student.FirstNames;
    Your problem is not about being able to grasp this. Your problem is me not being on the same page. I'm beginning to think I'm spending too much time in this and another forum, which is making me a bit sloppy.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45

    My Database attached

    Find a portion of the Access Database attached.

    I have shredded a lot of info out to let you see what it is I'm looking at. Once we get this right then I will be able to learn more.

    The combo box that holds all of the dates is in frmProvExamRoster. Presently you see all the students scheduled to write their exam on 19 May 2016 as this is coded into the criteria as #05/19/2016#. But I want to be able to have everybody have the ability to change the roster by changing the date.

    Micron - I am still trying to go through the things that you suggested but so far nothing has worked.

    I really do appreciate all those that have tried to help.

    Rick
    Attached Files Attached Files

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Some of it is simple, and some not. Here's how I fixed it.
    - put this in the query date field [Forms]![frmProvExamRoster].[cboSerial]
    - from property sheet, create AfterUpdate event for the combo & enter this one line: Me.Requery
    - then, remove the data source from the form, save, close, reopen in design view and reinstate the data source. Save, go to form view and it should work.
    I can only conclude that the form is mildly corrupted because it would not work for me until I reset the data source. I suspected corruption because it wouldn't work after doing the simple changes.

    I'd advise you to create a new db and import everything into it and go from there. The corruption is likely in a system table, which may or may not be resolved when you do what's prescribed above.
    Edit: just 2 cents - your date label looks like a control; not 100% intuitive like that. I'd avoid making them look the same as controls (i.e. no border or background colour). Just an opinion.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    There we go!
    Thank you so much Micron for all the time, effort and patience with me on this.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No problem. Sorry that i missed the boat on this the first time.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Plus minus drop down menu
    By ashu.doc in forum Forms
    Replies: 3
    Last Post: 08-09-2012, 10:04 PM
  2. Drop Down Help Menu
    By dandoescode in forum Access
    Replies: 1
    Last Post: 08-07-2012, 02:51 AM
  3. Drop down menu help
    By whojstall11 in forum Access
    Replies: 6
    Last Post: 03-07-2012, 01:51 PM
  4. Replies: 3
    Last Post: 11-29-2011, 07:01 AM
  5. Drop Down Menu Parameters
    By spoonman in forum Programming
    Replies: 5
    Last Post: 08-18-2011, 02:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums