Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: How to query with a combo box with < and > criteria?

    I'm doing a payroll interface now and I need to get the first half of the month(1-15) and the second half of the month(16-31) separately. I have an Access query that's already been created and now I need to get the combo box with the <=15 or >=16 values to put into the Access query criteria as [Form]![FormName]![Combo Box Name]. Month, day, and year is on a separate field but the day datatype of 'NUMBER.'
    Last edited by lansing; 09-08-03 at 11:56.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: How to do a combo box with < and > criteria?

    Just make a combobox with two items: "< 16" and ">= 16", and use the text property to form your query like:

    "SELECT * FROM PayRoll WHERE Day " & Combobox.Text

    You still need to specify your month and year.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Apr 2003
    Posts
    280

    Re: How to do a combo box with < and > criteria?

    Originally posted by DoktorBlue
    Just make a combobox with two items: "< 16" and ">= 16", and use the text property to form your query like:

    "SELECT * FROM PayRoll WHERE Day " & Combobox.Text

    You still need to specify your month and year.
    Yes, I have already did the month and the year. I will not be hardcoding this in VBA. I"m just using the Access query(or whatever you call it, if there is a term for it, please tell me) to get my information for the report.

    I have created a table for the days and it is bound to the second column. The DayText and DayValue field datatype is set to 'TEXT'.
    DayText DayValue
    1-15 <=15
    16-31 >=31
    All *

    In the combo box, I display the first column(DayText) and bound it to the second column(DayValue) when the user click on it.

    In the Acess query with the Day column for the criteria I put [Forms]![ExpenseForm]![cboDay]

    http://members.cox.net/tripnotic/samples/dayquery.jpg

    I'm still stuck on this! Need help.
    Last edited by lansing; 09-08-03 at 11:40.

  4. #4
    Join Date
    Apr 2003
    Posts
    280
    I put the wrong words for the title. I know how to create a combo box with the value in it. The title should says, "How to query out data with a combo box with < and > criteria?" Sorry!

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Ohh, I see your problem.
    Assume for the short that your combobox reference is c, and that your values may be "1st", "2nd", and "all".

    Then your condition may look like

    between iif(c="2nd",16,1) and iif(c="1st",15,31)
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Apr 2003
    Posts
    280
    Originally posted by DoktorBlue
    Ohh, I see your problem.
    Assume for the short that your combobox reference is c, and that your values may be "1st", "2nd", and "all".

    Then your condition may look like

    between iif(c="2nd",16,1) and iif(c="1st",15,31)
    Well, I have try it your way and I still can't get it to work. The only solution I have is to create three query for each value. I don't want to go this route, I would only like one query only.

    If you have time, can you create me a sample database with the query that you think will work with my problem and post it up here. Thanks!

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I'm sorry, I don't have that amount of time. It's easier when you post your query as SQL as far as you tried. Also post your error message or whatelse happened. I will find out what you did wrong, since my method should work.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Apr 2003
    Posts
    280
    This is what I have and it only pull up if the day 16 if it is true else 1.
    IIf(([Forms]![ExpenseForm]![cboDay]=">16"),16,1)

    I need the range of 1-15 and 16-31 and * (as in all for the month)
    Last edited by lansing; 09-10-03 at 16:14.

  9. #9
    Join Date
    Apr 2003
    Posts
    280
    Well, I got it to work. Thanks!

    I use your method and add a field to it when it is true. Since this only show the value -1(yes) and 0(no). I have to query all of the 0 out and also added the day field into the query so it would display the day of the record.

    DDs = custom field.


    DDs: IIf(([Forms]![ExpenseForm]![cboDay]="1-15"),[DD]<=15,IIf(([Forms]![ExpenseForm]![cboDay]="16-31"),[DD]>=16,IIf(([Forms]![ExpenseForm]![cboDay]="ALL"),[DD])))

    Criteria: Not 0

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    You put the expression into the field list, and my proposal was to put it into the criteria of your day field. But anyway, this works, too.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Apr 2003
    Posts
    280
    Well, I try it in the criteria but it doesn't work.

Posting Permissions

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