Help with query

[Wilse]

Can the combo box be set on form load show this month?

 

[SOS]

And trust us - we're TRYING to help but sometimes it is hard to do until we all get on the same page

 

[SOS]

Can the combo box be set on form load show this month?

Yes, it can. If you use the numbers, then in the form open event (not property - event) you can use:

Me.cboMonth = Month(Date())

and the year one can be

Me.cboYear = Year(Date())

 

[Wilse]

yea I know guys,

I was put on an access course through work which basicly showed this is a table, form, query and report.

 

[vbaInet]

Private Sub Form_Load()
dim monthVal as Date
DoCmd.Maximize
monthVal = Date()
End Sub

That's the correct way. It's a good habit to declare the variable (Dim monthVal as Date). The variable name doesn't matter as you've already figured.

I just looked at your db again and I noticed you still have one of your fields as DATE. Remember I advised not to use that Wilse?

 

[Wilse]

does the monthname function work instead so I get March, April ect or wont it filter

 

[vbaInet]

You're using Access 97 right? I don't think it's available in that version. It's from 2000 up.

 

[SOS]

Okay, what value do you have stored in your table? We keep coming back to this. If it is a DATE then you can use the NUMBER to get it. If you are storing TEXT then it isn't a date and we need to do something completely different.

 

[Wilse]

its date, i am using 97, our work doesnt like to keep up with the times

 

[SOS]

I'm in the database right now. I didn't notice before that it was uploaded.

First of all Which form should we be looking at? I looked at the Tran but it doesn't have any data in the database for that (there is no SECTION named "Transport" in the sample data, just F and L).

 

[Wilse]

I did have data but removed some earier. The main form is Main Menu and the query is Data Query

and I am going to remove all the macros and replace with VB coding

 

[vbaInet]

... and I am going to remove all the macros and replace with VB coding

BACKUP first

I can't remember if '97 gives you the option to convert the macro to vb. Look carefully at the menu options.

 

[SOS]

Well, basically all of your command buttons have this in there:

"[Date]>=#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext), -1) & "# AND " & _
        "[Date]<=#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext) + 1, 0) & "#"

And the -1 in the first part is wrong and should be a 1 not a -1.

But I still go for:

"[Date] Between #" & DateSerial(Year(Me.monthtext), Month(Me.monthtext), [COLOR=red][B]1[/B][/COLOR]) & "# AND " & _
"#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext) + 1, 0) & "#"

But also your FIELD names in the DATA table should NOT use DATE or SECTION as those are Access Reserved Words.

 

[Wilse]

i am just going through and changing the date ones

 

[Wilse]

but once the query filters by the combo box I wouldnt need the button to run the filter it would be done as long as everytime the date is changed i rerun the query, is that right?

 

[SOS]

Well, the button opens the form with the filter. So, in order to change the filter you would EITHER need to close the form and reopen it with the new date value or change the filter by using

Forms!YourOpenedFormName.Filter = ..and then put the same filter code here as what you used to open it with and then you use

Forms!YourOpenedFormName.FilterOn = True

 

[vbaInet]

i am just going through and changing the date ones

Just a heads up, the queries, form controls and report controls that include the previously DATE field would need to be changed too.

 

[Wilse]

But as the forms source data is form the Data Query if the date is changed it would change the the forms data as long as the query is ran before the form is open

 

[Wilse]

back to the combo box I set rom source type to value list and use 1;2;3;4;5;6;7;8;9;10;11;12 and repeat for years 10;11;12 ect.

 

[SOS]

back to the combo box I set rom source type to value list and use 1;2;3;4;5;6;7;8;9;10;11;12 and repeat for years 10;11;12 ect.

For the YEARS one, don't manually put it in, but instead use code in the form's On Load event.

If you have 2002 or above:

Dim iYrItem As Integer
 
    For iYrItem = Year(Now) - 3 To Year(Now) + 1
        Me.cboYear.RowSource = Me.cboYear.RowSource & Str(iYrItem) & ";"
    Next iYrItem

and that will get you a starting point of 3 years ago to next year (it will be DYNAMIC based off of the current year). You can modify as you wish.

If you have 2000 or prior:

Dim strYear As String
Dim iYrItem As Integer
 
For iYrItem = Year(Now) - 3 To Year(Now) + 1
     strYear = strYear & CStr(iYrItem) & ";"
Next
strYear = Left(strYear, Len(strYear)-1)
 
Me.cboYear.RowSource = strYear