471,853 Members | 1,837 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,853 software developers and data experts.

Greater than Date from Form - Append Query

I have a search form that a user can enter a date and return all records with that test date. Currently it only searches for records matching that date. I would rather it search for all test "greater than" that date.

Currently in my Append qry I have
Field: TestDate: [Forms]![PBTSearchF]![lfTestDate]
Append To: Test Date

Any help is appreciated.
May 11 '15 #1
9 1617
Seth Schrock
2,965 Expert 2GB
So you are copying records from one table (that has a date that you are wanting to filter based on the test date) to another table, correct? If that is the case, then you need to put your form reference in the criteria for the date field that you want to filter based on. To get the Greater Than criteria, just put > before the form reference.
May 11 '15 #2
No, I only have one table with the test information "tblinformation" then I have qryinformation and qryAppendInfo.
May 11 '15 #3
12,516 Expert Mod 8TB
The last sentence of Seth's post is the answer you're looking for. But I question the need to append the search results to a table. There's usually no need to do that at all, you can just display the results of a query without putting it in a table.
May 11 '15 #4
Not really understanding where to put the greater than. symbol with my reference. I tried putting it in the AppendQry looking like
Field: Test Date: [Forms]![fdlgPBTSearch]![lfTestDate]
Append To: Test Date
Criteria: >[Forms]![PBTSearchF]![TestDate]
Than in my QryInfo I just have
Field: TestDate
Table: tblInformation
May 11 '15 #5
5,501 Expert Mod 4TB
Open your query in design view.
Right click in the table area and switch to SQL view
Select all of the text there and copy to the clipboard

Return here.
Please, click on the [CODE/] button in the post toolbar and then cut and paste your script between the [code] [/code] tags
May 11 '15 #6
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![fdlgPBTSearch]![lfTestDate] DateTime;
  2. INSERT INTO tblpbtinformation (TestDate)
  3. SELECT [Forms]![fdlgPBTSearch]![lfTestDate] 
  4.    AS TestDate
  5. WHERE (((
  6.    [Forms]![fdlgPBTSearch]![lfTestDate])
  7.     >
  8.    [Forms]![fdlgPBTSearch]![lfTestDate]));
May 11 '15 #7
5,501 Expert Mod 4TB
1) Rabbit's question - Post#4 about storing this information... Rabbit's very knowledgeable about database/data management. Is there a good reason that you are storing this information? Doing so would break the rules for normalization.

2) Take a look at lines 6 and 8 in your post... you will never meet this condition.
May 11 '15 #8
1) I don't know. I'm not very knowledgeable about this and am basically altering someone's work from before who is no longer here.

2) Right, so this is wrong but how would I change it to do what I would like.
May 11 '15 #9
5,501 Expert Mod 4TB
+ So the broken database design is another thread; however, what you are dealing with is a prime example why it is so important to get the primary design correct from the start. The link to normalization is the foundation. I will also PM you my boilerplate of what I hope are useful tools and tutorials.

+ Let's take a look at:
Expand|Select|Wrap|Line Numbers
  1. [Forms]![fdlgPBTSearch]![lfTestDate])
  2.      >
  3.    [Forms]![fdlgPBTSearch]![lfTestDate]));
Right now you are referring to only the form in a circular reference. Nothing you are doing with logicals will ever fix this issue.
Expand|Select|Wrap|Line Numbers
  1. [PrimaryKey]    [TestDate]
  2. 1                01/01/2015
  3. 2                01/02/2015
  4. 3                01/03/2015
  5. 4                01/04/2015
  6. (...)
So in record [PK]=1 you are testing for [TestDate]>[TestDate] == [01/01/2015] > [01/01/2015] = false
same in [PK]=2,3,4...

The root issues are in both your parameter and your select statement. To work your way out of this I would advise you to create a simple select query (ignore append/insert etc...) Get this query to return the subset that you are after - something like this

(Basic SQL Syntax for Access Queries )

Expand|Select|Wrap|Line Numbers
  1. SELECT [tble1]![lfTestDate] AS "TestDate" 
  2. FROM tble1
  3. WHERE [tble1]![lfTestDate]>[User Entry]);
(note the [User Entry] will popup a VERY simple input box. This is eventually where your form reference might end up in the final query.

Once you have the select query working (change "tble1" etc... to match your database) then you can automate using the form to return the records you are after. Once that is working - then use it to append to your table (which isn't the best practice for most things)
May 11 '15 #10

Post your reply

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

Similar topics

2 posts views Thread by John | last post: by
2 posts views Thread by Ray Holtz | last post: by
reply views Thread by YellowAndGreen | last post: by
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.