Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    23

    Unanswered: query many fields


    Is there a way to do a query on many fields in one table with the name Date1,Date2,Date3,Date4,Date5,Date6,Date7. Can I do a parameter query and have a question come up "Enter Beginning Date" and "Enter Ending Date" where all of the fields are checked at once and the range of dates would come up on a query. I tried entering on the criteria line Between[Enter Beginning Date] and [Enter Ending Date] on each of the fields mentioned above but nothing comes up when you run the query.

    Any other suggestions?

    Thanks,
    Debit19

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yes there is ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2005
    Posts
    23
    Okay...do you have a suggestion? Thanks

    Debit19

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    did you try the OR sub clause each separate date condition as suggested in the previous post you made on this subject?

    Alternatively you could rephrase your date bracket
    as
    testdate>=date1 and testdate<=date7 seeing as date2,3,4,5 & 6 are sequential and in ranger date1 to date7

  5. #5
    Join Date
    Jan 2005
    Posts
    23
    I tried the "or" function but doesn't produce desired results. I like the idea of the second part of your answer. I don't know exactly how to insert the >= and <=. Here is my sql:
    SELECT trans.LastName, trans.FirstName, trans.MiddleInitial, trans.SocSecNum, trans.TypeOfServiceDay1, trans.ProviderLastName, trans.ProviderFirstName, trans.DOS1, trans.DOS2, trans.TypeOfServiceDay1, trans.TypeOfServiceDay2, trans.Day1Start, trans.Day1Finish, trans.Day2Start, trans.Day2Finish
    FROM trans
    WHERE (((trans.DOS1) Between[Enter the Starting Date] And [Enter the Ending Date])) OR (((trans.DOS2)Between[Enter the Starting Date] And[Enter the Ending Date]));

    If I get this to work for DOS1 and DOS2 (Date of Service1 and 2), then I can get the rest to work.

    Thanks,
    Debit19

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Personally I don't like the double paramter request, you are risking someone putting incorrect dates either inadvertantly or mischieviously

    if your dates are sequential then
    ....WHERE trans.DOS1 >= " & cdate([Enter the Starting Date]) & " And trans.DOS2 <=" & cdate([Enter the Ending Date]) & ";"

    failing that It'd use a form to capture the dates and then refer the query to those variables

    eg
    ....WHERE trans.DOS1 Between " & cdate(forms!frmParamters.Startdate) & " And " & cdate(forms!frmParamters.Endate) & " OR trans.DOS2 Between " & cdate(forms!frmParamters.Startdate) & " And " & cdate(forms!frmParamters.Endate) & ";"

  7. #7
    Join Date
    Jan 2005
    Posts
    23
    Thanks for your help. I tried both methods and the error I get is the statement is too complicated to evaluate try using variables. Could this be caused by the lack of "(" in certain areas? Here is my portion of the sql:

    WHERE trans.DOS1 Between "&cdate(forms!frmParameters.StartDate)& " And " & cdate(forms!frmParameters.EndDate) & trans.DOS2 Between " & cdate(forms!frmParameters.StartDate) & " And " & cdate(forms!frmParameters.EndDate) & ";";

    Also notice the extra ; at the end I didn't put that there. If I need to use variables let me know where if you could and maybe that would work. I understand what you meant with the form.

    Thanks,
    Debit19

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •