473,326 Members | 2,255 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,326 software developers and data experts.

Queries - filtering by dates

53
hi, i was wondering could anyone help me with a query i have created. i have sucessfully worked out the total amount od prodcuts sold in a shop.
However i want a query to show the total sold of one product only in the last month.

First i made a query which summed up the total prodcuts sold, via the qty on order.



FIELD Prod_ID from QUERY - All customer order details

FIELD Qty_on_Order from QUERY - All customer order details (SUM)
FIELD Prod_Name from TABLE - Products

i then want to add the field "Recievied_Date", from the table Customer Order. this is in the date format e.g. 21/01/2007 and here in the crietria box i want to only show prodcuts which have been brought in the last month.

Please may anyone help me
Cheers in advance
Mar 26 '07 #1
3 1355
nico5038
3,080 Expert 2GB
You can use in the query a format statement like:

select format(yourdate,"yyyymm") as Yearmonth ....FROM tblX
WHERE format(yourdate,"yyyymm") = format(Date(),"yyyymm")

Nic;o)
Mar 27 '07 #2
panjap
53
sorry i do not undertant the expression stated. is this for the sql or within the query design. also i do not undertand the code, and how to adapt this to my date search.
does the solution not go in the criteria box?
please may you help me as i am really strugling
cheers for your help
Mar 28 '07 #3
nico5038
3,080 Expert 2GB
In the graphical query editor use for a field:

Yearmonth:format(yourdate,"yyyymm")

The sample was for use in SQL mode.

Nic;o)
Mar 28 '07 #4

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

Similar topics

7
by: Dana Shields | last post by:
I am attempting to upsize from access to SQL Server. I'm trying to convert my queries to SQL Server views; however, I'm having a lot of difficulty with the syntax differences. For instance, a...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
1
by: Ken | last post by:
I wrote a function to use in queries that takes a date and adds or subtracts a certain length time and then returns the new value. There are times when my function needs to return Null values. ...
4
by: John Smith | last post by:
Isn't life a bitch! You know what you want but you don't know how to get it. I have produced 12 queries that calculate a payment profile over 12 months. For a number of the records (ie with...
14
by: Kukurydz | last post by:
I've got such problem: My database is stored on MSSQL Server. I have to write reports for it in MSAccess. I've got a problem with creating a query which will select records from MSSQL table with...
0
by: MHenry | last post by:
Hi, I know virtually nothing about creating Macros in Access. I would appreciate some help in creating a Macro or Macros that automatically run(s) 14 Queries (three Make Table Queries, and 11...
3
by: Zlatko Matiæ | last post by:
Hello. I'm wondernig what is happennig whith saved pass-through queries nested in regular JET query if regular JET query just filtrates result by start/end date...Does pass-through query first...
3
by: iamguyster | last post by:
Hi, I have an exercise I need to give to my pupils (I'm a teacher!) and I am trying to get a query working preferably using the query design view, without having to edit the SQL. The query involves...
0
debasisdas
by: debasisdas | last post by:
This article consistes of some of the frequently asked date related queries. Hope the users find it useful. ========================== 1.Determining the Date of the First and Last Occurrence of a...
2
by: Big X | last post by:
Hi, I am having a little trouble with some data I have been sent. Seems they want me to remove all the date that have a 5 year expiry date. I receive the data in csv file so the properties of the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
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...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.