Results 1 to 3 of 3
  1. #1
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112

    Parameter querys - how to prompt for date parts and convert text to dates

    Hello,
    I would like to develop a parameter query that does the following:


    Prompts for [Beginning with Fall (F) or Spring (S) Semester?] and then converts the response into 9/1 or 1/1 (September 1 or January 1, respectively)
    And then prompts for [Beginning with what year (yyyy)]

    And then assembles that date and produces a list of records that have that have an [EnrollmentDate] greater or equal than that date.

    I need to put this on the criteria line or create a field in the query, rather than do it with SQL coding.

    Could you give me some suggestions about this?

    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Use a form.
    user can pick the block in a combo box,
    you assemble the dates to text boxes, txtStartDate, txtEndDate.
    then the query is:
    select * from table where [date] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Another way is to do it in the query. FieldName: IIf([Beginning with Fall (F) or Spring (S) Semester?]="F",CDate("09/01/" & [Enter Year as YYYY]),....

    If you do it through a form you can do error checking, in case they don't enter an F or an S for instance. Also with a query, if they make a mistake while entering they are pretty much screwed!

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

Similar Threads

  1. Replies: 3
    Last Post: 06-10-2015, 10:12 AM
  2. Convert Text Column To Date
    By jo15765 in forum Queries
    Replies: 6
    Last Post: 01-16-2015, 03:41 PM
  3. Replies: 5
    Last Post: 02-06-2014, 01:11 PM
  4. Convert Text String to Date in SQL
    By kestefon in forum Access
    Replies: 2
    Last Post: 12-04-2013, 03:33 PM
  5. Replies: 3
    Last Post: 09-11-2013, 09:49 AM

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