Greater than Date from Form - Append Query

[JamesHalko]

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.

[Seth Schrock]

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.

[JamesHalko]

No, I only have one table with the test information "tblinformation" then I have qryinformation and qryAppendInfo.

[Rabbit]

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.

[JamesHalko]

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

[zmbd]

Please:
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

[JamesHalko]

PARAMETERS [Forms]![fdlgPBTSearch]![lfTestDate] DateTime;
INSERT INTO tblpbtinformation (TestDate)
SELECT [Forms]![fdlgPBTSearch]![lfTestDate] 
   AS TestDate
WHERE (((
   [Forms]![fdlgPBTSearch]![lfTestDate])
    >
   [Forms]![fdlgPBTSearch]![lfTestDate]));

[zmbd]

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.

[JamesHalko]

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.

[zmbd]

James,
+ 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:

[Forms]![fdlgPBTSearch]![lfTestDate])
     >
   [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.

[PrimaryKey]    [TestDate]
1                01/01/2015
2                01/02/2015
3                01/03/2015
4                01/04/2015
(...)

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 )

SELECT [tble1]![lfTestDate] AS "TestDate" 
FROM tble1
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)