How to use user input to define query criteria?

[AccessThis]

Hi, I have a table that lists customer records and I have a query set up that filters these records based on the date i.e. between #date# and #date#



I also understand that there is a built in function to filter as I want in microsoft access 2007 and up, but the people using the database will not be savvy enough with access to learn how to use all of these functions, so I was wondering how to build a form that takes in the user's input.

What I have in mind comes down to this: I want to have a text box that the user types in two dates, and then presses a button to launch a query that will read in the two dates and use them as the criteria for the date filter.

I'd imagine work needs to be done on both ends for the form and the query, but I'm not sure what the term is for what it is I am trying to do. If someone could give me a term to look up or refer me to a site, or even better, tell me how to do this, I'd be very grateful. Thanks a lot.

Hmm I would upload my database (Since there is no personal information on it), but it seems like I cannot upload .accdb files and I don't think I can downgrade the file to .mdb

[pbaldy]

What you're looking for is called a parameter query. After setting up your form, the query would look like:

Between Forms!FormName.FirstTextboxName And Forms!FormName.SecondTextboxName

[AccessThis]

Thanks a lot. Worked out great, but I ran into slight trouble doing something slightly different:

Basically, I have a conditional column listed as AR (It is a Yes/No).

I have a query set up to filter out all of the ARs that are listed as Yes, and with that, I calculate the AR_Amount column as the SalePrice column - the NetPrice column.

However, I am trying to figure out how to add a condition so my logic breaks down to this:

If AR = Yes

If Payment = unpaid

AR_Amount = SalePrice

Else if Payment Does not = unpaid

AR_Amount = SalePrice - NetPrice

The problem I am having is that I want the AR_Amount column to update as the information is typed in so it can be displayed on the form that it is being typed into so that the user is sure that the amount is correct, otherwise they have to manually change it themselves.

Basically, I want to add criteria in one field based on another field. Is this possible?

So I would have AR_Amount set to a default value (SalePrice - NetPrice) that would only change if the field for payment was "unpaid" in which case it would then be simply SalePrice. I'll keep working on it and update if I figure anything out.

One last question: Is it possible to manually change the value for a query? For example, if every value in the query is automatically generated on values that I typed into a table, but a few rows are incorrect due to arbitrary changes made through sales, is it possible for me to manually change them to the corrected values despite the fact that the field is supposed to bet Sale - Net?

So say Sale - Net comes out to $10, but I want it to be $20, can I change it to $20 manually without conflicting with the query's programmed value? (These are features available on microsoft excel so I was wondering, otherwise what would be the best way to go about accomplishing this?)

[AccessThis]

Ok, I figured out the if statement for anyone that was wondering.

Used the iif command to accomplish my task:

AR_Amount: IIf([fp]="up",[sale],[sale]-[net])

Which is, if the field FP has the term "UP" written in it, display the Sale field, otherwise display the sale field minus the net field.

So at the moment, I kind of accomplished what I wanted, but I'm still having issues.

I wanted to display the AR_Amount on my main form corresponding to each record, but instead, it is only displaying the first value of AR_Amount for every record.

I created a textbox and typed this as the data source:

=DLookUp("[AR_Amount]","[Account Receivable]")

I also tried to requery by added in the event after update with the code:

Private Sub AR_A_AfterUpdate()

Me!AR_A.Requery

End Sub

But that did not work either. (AR_A is the name of the text box I created)

I am not really sure where the problem lies, but I will try messing around with relationships to see if maybe that will fix it.

[pbaldy]

Sorry, I lost track of this thread. The DLookup would require a criteria:

DLookup Usage Samples

[AccessThis]

Hmm, I'm a bit confused on how specifying criteria would fix the issue though... I tried messing with the criteria a bit and I kept getting the same results i.e. first record being shown for all records on the table. Here is a picture of what I am talking about:



See, the records where AR is not checked should display 0.00 or nothing and all the values should be different for the ones where AR is checked since there are different values place in Sale and Net.

Anyway, I'll keep reading up on forms/queries and try to figure this out and keep you updated.

[AccessThis]

So I figured out how to make each record display correctly by using the command:

IIf([ar]=Yes,IIf([fp]="up",[sale],[sale]-[net]),Null)

But the problem is that this command doesn't call from the query so changing the values manually isn't going to change the values in the query.

Would you suggest that I use the form! command and have the query's value determined based on the textbox? I'll give it a shot anyway and get back to you guys! lol.

Edit: Ok, so that method is out the window because I can't even change the values in the field if I use the iif command.... It looks like I need to fix how I'm using the dlookup() function because I don't think the iif statements are going to work out.

Is there a way to change the values of the query??? I think the above code would work fine if only I were able to change the values manually by clicking on a record and typing a different value, but the query will not allow me to change anything because the values seem hardcoded....