473,378 Members | 1,531 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 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 1831
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

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

Similar topics

by: John | last post by:
Hi - I am trying to perform a simple append query, with no luck. I have a table (MktPrices) that has the following fields: BondID, PriceDate, Price. The objective is to allow the user to input a...
by: Ray Holtz | last post by:
I have a form that shows a single record based on a query criteria. When I click a button it is set to use an append query to copy that record to a separate table, then deletes the record from the...
by: Lee | last post by:
Hello All, First of all I would like to say thank you for all of the help I have received here. I have been teaching myself Access for about 4 years now and I've always been able to find a...
by: DHarris | last post by:
I created a continuous form in Access2003 based on a query that users review customers and once reviewed clicks on a command button that executes an append query to update a table of the reviewed...
by: dick3425 | last post by:
How do I pass a date from a form to an append query in Access 2003 using DAO? I want to use a form and a combo box to choose the date and pass that date to an append query. The date field in the...
by: Dan2kx | last post by:
Still duin the holiday database... and i have discovered something that i cant explain... can you guys? ok i have a procedure which basically (quite a longwinded process) selects the start date...
by: Scott12345 | last post by:
Hi, here is my situation, I have a DB that tracks machine downtime (30 machines) per day. Several users will update this through the day. I created an append query that creates 30 dummy values and...
by: iheartvba | last post by:
Hi, I have a simple append query which takes data from a form and appends it into a table. INSERT INTO tblSmsSent ( MobileNumber, ClientName, TimeSent, AppointmentTime, AppointmentDate )...
by: Hishow | last post by:
Dear Professionals, Please I need help again. I have an append query that I run every month on a form using a button(with vba). Append query is named (AppData). On that form(FormA), I have an...
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.