By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,259 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,259 IT Pros & Developers. It's quick & easy.

Get date from textbox into query and use it as join

100+
P: 294
The user is entering a start date and end date to filter records.

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

Expand|Select|Wrap|Line Numbers
  1. Date (PK)        Value
  2. 3/31/2013        $1,975.00
  3. 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!
Apr 29 '14 #1
Share this Question
Share on Google+
10 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 29 '14 #2

zmbd
Expert Mod 5K+
P: 5,397
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. (^_^)
Apr 29 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
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.
Apr 29 '14 #4

100+
P: 294
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.
Expand|Select|Wrap|Line Numbers
  1.  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)

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. , (Select NetAssetValue FROM Nav_Tbl WHERE NAV_Date = Forms!PlanFrm!ReportSelectSbfrm.Form!StartDateTxt) AS [BeginNAV]
  3. , (Select NetAssetValue FROM Nav_Tbl WHERE NAV_Date = Forms!PlanFrm!ReportSelectSbfrm.Form!EndDateTxt) AS [EndNAV]
  4. FROM AwardTbl
  5.  

Thanks to Seth and zmbd for their efforts (per usual)!
Apr 29 '14 #5

Seth Schrock
Expert 2.5K+
P: 2,951
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?
Apr 29 '14 #6

100+
P: 294
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..

Expand|Select|Wrap|Line Numbers
  1. Select Forms!PlanFrm!ReportSelectSbfrm.Form!StartDateTxt AS [StartDate]
Apr 29 '14 #7

Seth Schrock
Expert 2.5K+
P: 2,951
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?
Apr 29 '14 #8

100+
P: 294
Just display the value the user entered into the text boxes on the form.
Apr 29 '14 #9

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Apr 29 '14 #10

100+
P: 294
Oh that actually works! Haha thanks, Seth. I appreciate your help!
Apr 29 '14 #11

Post your reply

Sign in to post your reply or Sign up for a free account.