Help with query

[Wilse]

This is when I select the month from the combo box. I get that error

 

[SOS]

This is when I select the month from the combo box. I get that error

Okay, again I will tell you - the combo boxes need to be UNBOUND. Do NOT set their CONTROL SOURCE property to be anything. If there is a field name in there right now, take it out.

The combo's ROW Source property should have something (that's what tells it what to display in the drop down) but the CONTROL SOURCE property tells it where to store it in the table and with these two controls you do NOT want to store it in a table.

 

[Wilse]

there is no control source

 

[vbaInet]

Do you have an input mask? Did you actually select or type it in?

Have you got the Row Source Type property set as Value List?

Bit slow today aren't I

 

[Wilse]

no imput mask,

for some reason it has set a format to date, I have now removed it

 

[Wilse]

I have now added on the combo boxes on enter the coding to display this month and years so now its onto the query

Between DateSerial(Year([Me].[cboYear]),Month([Me].[cboMonth]),1) And DateSerial(Year([Me].[cboYear]),Month([Me].[cboMonth])+1,0) is for the same form,

would it be

Between DateSerial(Year([Me].[forms]![mainmenu]![cboYear]),Month([Me].[forms]![mainmenu]![cboMonth]),1) And DateSerial(Year([Me].[forms]![mainmenu]![cboYear]),Month([Me].[forms]![mainmenu]![cboMonth])+1,0)

 

[SOS]

No, if done in the query you have to use the FORM reference, NOT ME. ME is only good in VBA.

 

[SOS]

And by form reference I mean:

[Forms]![YourFormName]![YourControlName]

like that including the brackets and leaving the [Forms]! part exactly as shown.

 

[Wilse]

Between DateSerial(Year([forms]![mainmenu]![cboYear]),Month([forms]![mainmenu]![cboMonth]),1) And DateSerial(Year([forms]![mainmenu]![cboYear]),Month([forms]![mainmenu]![cboMonth])+1,0)

 

[SOS]

Between DateSerial(Year([forms]![mainmenu]![cboYear]),Month([forms]![mainmenu]![cboMonth]),1) And DateSerial(Year([forms]![mainmenu]![cboYear]),Month([forms]![mainmenu]![cboMonth])+1,0)

YES!

That's it Watson

 

[Wilse]

it doesnt show me anything for this month and i have 1 record

 

[SOS]

MY BAD -

Forgot since we changed to the combo boxes we don't need the date functions (year and month) in there.

Try this instead:

Between DateSerial([forms]![mainmenu]![cboYear],[forms]![mainmenu]![cboMonth],1) And DateSerial([forms]![mainmenu]![cboYear],[forms]![mainmenu]![cboMonth] +1,0)

 

[Wilse]

What a star, thanks so so much, now if I use after update to rerun the query it should update each time its changed

 

[Wilse]

Is this ok, or is there a better way

Private Sub cboMonth_AfterUpdate()
DoCmd.OpenQuery "Data Query"
DoCmd.Close
End Sub

 

[SOS]

What's the point of opening and then closing the query? Isn't it a FORM you are opening? If so, since you essentially filtered the form, you need to either change the form's filtering or you need to close the form and reopen it with the new date values.

 

[Wilse]

Between DateSerial([forms]![mainmenu]![cboYear],[forms]![mainmenu]![cboMonth],1) And DateSerial([forms]![mainmenu]![cboYear],[forms]![mainmenu]![cboMonth] +1,0)

now gives me

"This expression has been typed incorrectly, or is too complex to evaluate..."

 

[Wilse]

if i delete the

Between DateSerial([forms]![mainmenu]![cboYear],[forms]![mainmenu]![cboMonth],1) And DateSerial([forms]![mainmenu]![cboYear],[forms]![mainmenu]![cboMonth] +1,0)

and then open the mainmenu form then add criteria back in it works until the mainmenu form is closed and reopen or the database is closed and opened.

Any ideas

 

[vbaInet]

The main menu form must be open for it to work.

 

[Wilse]

It wont open I get that message

 

[Wilse]

uploaded if anyone wants to look