I am using MS Access 2007
I have a transaction table which holds all records with posting date on each of them. I used a make table query to find out the max date hold in the transaction table and use the dateAdd function to find the date with 1 month, 5 months and 6 months ago and export it into a table name TRSMPD that only have one record.
I then use the TRSMPD table to query the transaction table to find the records that has dates greater than the value in the 1mth field.
The TRSMPD table structure is
Field Name - MaxOfPOSTING_DA
-
1mth
-
5mth
-
6mth
with data type Date/Time
I build the following query to find the records but it returns no records - SELECT Transactions_File.PLANT, Transactions_File.MATERIAL_N, Transactions_File.POSTING_DA, Sum(([QUANTITY_I]*-1)) AS Qty
-
FROM Transactions_File LEFT JOIN TRSMPD ON Transactions_File.POSTING_DA = TRSMPD.MaxOfPOSTING_DA
-
WHERE (((Transactions_File.PLANT) Like "DC*") AND ((Transactions_File.POSTING_DA)>[TRSMPD]![1mth]))
-
GROUP BY Transactions_File.PLANT, Transactions_File.MATERIAL_N, Transactions_File.POSTING_DA;
I have test it with a date value to replace the 1mth field name as criterna and it works.
It also works with input field for date criteria
I also tried using DateSerial and Format function in the criteria and Access gives me error message telling data type mismatch
When I use CDate function, Access error message tells Invalid use of Null
I want to make this query fully automatic that will update when new records are added and does not require any manual input when the query runs. Please advise.
7 3470 ChipR 1,287
Recognized Expert Top Contributor
Try replacing [TRSMPD]![1mth] (you just can't do that) with:
(SELECT MAX(1mth) FROM TRSMPD)
NeoPa 32,568
Recognized Expert Moderator MVP
If you have a single record in the table of a global nature (IE. That record pertains to all data), then there is no need for a JOIN in your FROM clause. You simply need to separate the two tables with a comma (,).
NeoPa 32,568
Recognized Expert Moderator MVP
I suspect the shriek (!) character may cause a problem, but you would want to use [TRSMPD].[1mth].
Hi ChipR,
The query give me a syntex error message when using the code you suggest.
By the way the TRSMPD table only have one record, so, I don't understand why I need to use max function.
Hi NeoPa,
All I need to get is a list of records from transaction file that has the posting date from the most current date backward to one month ago in the record. eg. most current date in record is March 15, 2009, then I need to list all records that is greater than February 15, 2009.
I change the shriek to dot but still not getting any record from the query.
Any other way I can try ?
NeoPa 32,568
Recognized Expert Moderator MVP
I want to make this query fully automatic that will update when new records are added and does not require any manual input when the query runs. Please advise.
The following logic would do as requested without the need for a table for the newest date. - Dim dteNewest As Date
-
Dim dte1Ago As Date
-
Dim dte5Ago As Date
-
-
dteNewest = DMax(...
-
dte1Ago = DateAdd("m", -1, dteNewest)
-
dte5Ago = ...
-
-
strWHERE = "WHERE (((Transactions_File.PLANT) Like 'DC*' AND ((Transactions_File.POSTING_DA)>" & dte1Ago & ")) "
-
-
strSQL = strSELECT & strFROM & strWHERE & strGROUPBY
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Russell |
last post by:
I'm having a fit with a query for a range of dates. The dates are
being returned from a view. The table/field that they are being
selected from stores them as varchar and that same field also stores
other fields from our dynamic forms. The field is called
'FormItemAnswer' and stores text, integer, date, float, etc. Anything
the user can type into one of our web forms. The query looks like,
select distinct from...
|
by: Akinia |
last post by:
Hi every body
I've a little problem with my query, I can't figure it out. I've three
tables:
Table A (EmployeNr, Date, Code)
Table B (EmployeNr, Date, Code) Which is an historic of Table A
Table C (BegD, EndD)
I can have all the records in table A that doesn't match in table B
but not from the other side.
|
by: rrh |
last post by:
I am trying to update a field in one table with data from another
table. The problem I'm running into is I need to base the update on a
range of data in the 2nd table.
Table 1 has:
date field
new field
table 2 has:
key field (autonumber)
|
by: C White |
last post by:
Hi
I am having problems with running a query that does the following
there are 5 fields in a table that the query is based on, the first four
are simple enough and all that happens is that the fields need to match,
for that I was able to do the following to get records out of the
database (does that make sense?)
In (SELECT FROM As Tmp GROUP BY
|
by: Maxi |
last post by:
There is a lotto system which picks 21 numbers every day out of 80
numbers.
I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)
Here is the structure and sample data:
"Date","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","P11","P12","P13","P14","P15","P16","P17","P18","P19","P20","P21"
1/1/2005,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
1/2/2005,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
| |
by: Don Sealer |
last post by:
I'm guessing this is pretty simple however not simple enough for me.
I'm developing a database to track expenses, income, banking
transactions, etc.
I have a very simple query with four fields, date, checking account
deposits, savings account deposits, savings account withdrawals. How do
I get this query to only show dates when a transaction occurred in one
of the other three fields?
Thanks,
Don..............
|
by: flumpuk |
last post by:
Hi
My job currently requires me to enter data from 300+ forms a month.
The system which we used in Excel was slow , and theprevious guy had
three workbooks for this job .
I have created a table in Access with four fields
|
by: jennwilson |
last post by:
Using Access 2000 - I have a query that is suppose to return the records from table within specified time range and find matching data from another table .
Table houses Clinician name, location and rate information
Table houses Clinician name, location and number of units for each type of service rendered on each day. The two tables are connected by field
Please see the code below
SELECT .Clinician, .Location, .Date,...
|
by: zion4ever |
last post by:
Hello good people,
Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet reservation of available resources (laptops, beamers, etc). The MySql database queries are already in place, as is the ASP administration panel. The frontend that users will see however, still needs some work. I'm really close, but since I'm no...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |