Produce the data from a selected file/qry.

[avalon60]

I have over 100 queries of weather data, for each month, and would like to display certain or filtered information for a selected month.

I have been playing around with a combo box to select a month from a given year, and was hoping that maybe an 'After Update' would do what I want. The problem I have is that I don't know how to do this, as the month picked and displayed in the combo box is variable , and so a sort of wildcard might do the job, if I knew how.

 

[plog]

I have over 100 queries of weather data, for each month,

That's not really how you should be doing things. You can filter a form or report using a Filter (https://msdn.microsoft.com/en-us/library/office/ff194672.aspx).

What you should do is build a master query with all your data, then use the Filter property to filter the form to a specific month.

 

[jdraw]

Further to plog's comment, you store data in tables. You can query that data and use criteria to restrict what is returned. But if you are using a Form (noted with the combobox reference) then selecting all data and using filters to get exactly what you want seems a good strategy.

 

[avalon60]

Ok, I have now got a form with 4 fields on , which is all I want for now. There is a read date field which obviously only shows 1 day. Is there a way I could have it to display a months worth of data.
Thanks

 

[jdraw]

Sure. But we need to know a little about your table. What are the fields in the underlying table(s)? Which fields do you want to display? Which month?

 

[avalon60]

The table has 6 fields:
ID PK
ReadDate
TempMaxC
TempMinC
RainFall
Conditions

There are just over 5000 records in it, and would like to maybe display a month's worth at a time.

 

[jdraw]

I don't know what you have tried, but here is some sql for a query

SELECT ID, READDATE, TEMPMaxC, TEMPMinC, RAINFALL, Conditions
from YourtablenameHERE
Where MONTH(READDATE) = [Enter the Month Number]
Order By READDATE

You have to use your own table name.
Put the code into the sql view of the query wizard.

It will ask you for the month number eg Jan would be 1, Feb would be 2, Mar would be 3............

Try it and post back.

 

[avalon60]

I have just tried a split form with date filters, which kinda works.

Using the SQL code, when I click on 'run' as it were, I am asked for READDATE from a parameter window, then asked for a month number, and after entering the values I get no records . Aah, but then I entered August 5th for the READDATE, then 8 for the Month Number and got several thousand records. Now I need to filter for the year as well.

I tried it by copying the WhereMONTH line to whereYEAR etc,but then go a syntax error.

 

[jdraw]

You said ReadDate was a field in your table. If that is the case, my query should not be asking you to submit a value.

What are some values for ReadDate in your table?

 

[avalon60]

Some example ReadDates:
01/03/2004
16/09/2008
11/02/2009
27/11/2010

The UK date is day first

 

[jdraw]

And if you use the SQL I provided to create a query, you say it is prompting you for ReadDate and [Enter the Month Number]

I do not understand why you are being asked for ReadDate.
I expected only [Enter the Month Number] to which you would enter 2 or 8 or whatever month number for which you wanted to see data.

 

[avalon60]

Yes it does ask for a ReadDate, as I have just tried it again.

 

[jdraw]

There is something wrong at your end.
I just built a table of your design ( I did add a field ID1 a autonumber PK via Access import) see attached.

Here is my query --- same as I suggested to you.

SELECT ID, READDATE, TEMPMaxC, TEMPMinC, RAINFALL, Conditions
FROM weather
WHERE MONTH(READDATE) = [Enter the Month Number]
ORDER BY READDATE;

Here is my test data

ID1	ID	Readdate	RainFall	TempMaxC	TempMinC	Conditions
1	1	21/03/2014	0	23	12	cloudy
2	2	22/03/2014	1	24	14	cloudy
3	3	26/03/2014	0	19	13	clear
4	4	02/04/2014	0	23	19	misty
5	5	03/04/2014	1	22	15	rain

And here is the result when I asked for month 4

ID	READDATE	TEMPMaxC	TEMPMinC	RAINFALL	Conditions
4	02/04/2014	23	19	0	misty
5	03/04/2014	22	15	1	rain

Are you sure of your field names?

 

[avalon60]

yes there was a small problem at this end as to why I was asked for a READDATE. My apologies as I should have said the field was ReadingDate and not ReadDate. I have other tables where I have ReadDate, and the one I used is ReadingDate, hence the confusion.

So now I am asked for a Month Number as per the sql, and get back 422 records , which is the said month for every year. Would it be possible to narrow it down to say a specific date or day, like August 6th.

Thanks

 

[avalon60]

Just wondered if I have to have a field called ReadYear in the table so as to select the year. I hope not as there are over 5000 records I would have to go through!

Thanks

 

[jdraw]

sure.

You really should investigate SQL WHERE clause

Currently
WHERE MONTH(READDATE) = [Enter the Month Number]

You could have
1. WHERE Day(READDATE) = [Enter the Day Number]

OR

2. WHERE MONTH(READDATE) = [Enter the Month Number] AND
Day(READDATE) = [Enter the Day Number]

OR
3. WHERE YEAR(READDATE) = [Enter Year To SELECT] etc.

You really have to sort out exactly what you are looking for. You have many options.

 

[avalon60]

This does exactly what I want now!

WHERE MONTH(READINGDATE) = [Enter the Month Number] AND
Day(READINGDATE) = [Enter the Day Number]

Thanks for your time and help

Oh, and yes I will investigate the WHERE Clause