Get date from textbox into query and use it as join

[mcupito]

The user is entering a start date and end date to filter records.

In the table, the data is set up like so:

Date (PK)        Value
3/31/2013        $1,975.00
12/31/2013       $2,000.00

If the user types in the begin date -> 3/31/2013 and 12/31/2013 for the end date, I am wondering how to get the values of $1,975.00 and $2,000.00 into a query. I am also trying to get the dates into the query as well.

I will be inserting this into a much larger query.

Does anyone have any experience with this?

Thanks a lot!

[Seth Schrock]

Lets assume that the user is entering the dates on a form named frmDateRange with the control names being txtStart and txtEnd. Also, since I'm not sure what the date field's name is, I'll call it DateField. So the WHERE clause of your query would be like this.

WHERE DateField BETWEEN Forms!frmDateRange!txtStart AND Forms!frmDateRange!txtEnd

The BETWEEN function includes the dates specified as well as those in between. To get the fields to display in the query, you just add the field names to the SELECT portion of the query.

[zmbd]

mcupito :
How are they entering the information?
Via form text fields?
Then, in VBA, you can pull and use that information either directly by reference to the controls or build the SQL string.

Also do you want a VBA solution or an SQL solution or a hybrid?

You should know by now that the better the initial information the better and faster the answers arrive. (^_^)

[zmbd]

Seth, you and I cross posted. (^_^)

I try to avoid the "assumption " phase of answering questions. Much better to start with a clear picture of the situation as it takes fewer posts to get to the solution.

[mcupito]

Yes, sorry. I always leave out an important detail - it never ceases to fail.

The date fields are on a form. Here's how I am using it for other things in my query.

 BETWEEN Forms!PlanFrm!ReportSelectSbfrm.Form!StartDateTxt AND Forms!PlanFrm!ReportSelectSbfrm.Form!EndDateTxt

I would prefer a SQL answer because that's what I am trying to throw it into. If I posted my SQL it would probably only lead to more confusion - but I can if it's needed!

I am kind of thinking it would be a subquery in the select statement? Possibly?

Edit
The subquery route worked - sorry for wasting anyone's time! Not sure why I had to ask a question in order to figure it out! This is what I went with (code omitted)

SELECT *
, (Select NetAssetValue FROM Nav_Tbl WHERE NAV_Date = Forms!PlanFrm!ReportSelectSbfrm.Form!StartDateTxt) AS [BeginNAV]
, (Select NetAssetValue FROM Nav_Tbl WHERE NAV_Date = Forms!PlanFrm!ReportSelectSbfrm.Form!EndDateTxt) AS [EndNAV]
FROM AwardTbl

Thanks to Seth and zmbd for their efforts (per usual)!

[Seth Schrock]

Are you receiving an error with this code or is it just not returning the records you are expecting?

**Edit** I just noticed the title of your question. What do you mean by using it as the join? What exactly are you trying to do?

[mcupito]

Well, to be honest I haven't done much SQL Development, so it is hard sometimes to put into words what I am trying to do.

I thought I needed to create a join (somehow, now I realize it's poorly worded) to the table with the Date and Value using the StartDateTxt and EndDateTxt.

The question should have said how to get the values from text boxes on a form into a query.

As in..

Select Forms!PlanFrm!ReportSelectSbfrm.Form!StartDateTxt AS [StartDate]

[Seth Schrock]

So are you needing to filter the results of the query by the values entered by the user or just display the value the user entered?

[mcupito]

Just display the value the user entered into the text boxes on the form.

[Seth Schrock]

Okay. Just be aware that it will be the same value for every single record. So all you should need to do to get the SQL in post #7 to work is add the FROM clause and make it be any real table name. Since you aren't actually getting any data from the table it doesn't matter which one in this case. Once you add it to your real query, then it would matter of course. Just for troubleshooting then, I would just try to get that little bit to work and then move it to the real query.

[mcupito]

Oh that actually works! Haha thanks, Seth. I appreciate your help!