access 2010 Query dates

[Rand]

access 2010 Query dates [SOLVED]

is their a way to have a query to only show data on every monday in current month.

Month([datefield])=Month(Now()) And DateAdd("d",7,[datefield])

maybe someone can help me out.
tnx

 

[CJ_London]

Use the weekday function - here is a link

http://office.microsoft.com/en-gb/access-help/weekday-function-HA001228933.aspx

so something along the following lines

where weekday(datefield)=2 and month(datefield)=month(now())

 

[Rand]

Nice it worked !
tnx a million i was stumped after trying different things, i figured i would need 4 queries
to do it. but now im getting an error when reading the query in vba sql recordset.
this statement works great in the query window but gives an error "3061 too few parameters -1" when Set rstEvents = db.OpenRecordset(strSQL)
strSQL=
SELECT wkday.emphrs.dte,wkday.emphrs.emp,wkday.emphrs.bs,wkday.emphrs.hrs FROM wkday WHERE (wkday.emphrs.dte) Between 41579 And 41608 ORDER BY wkday.emphrs.dte;

basically the query gets the data know problem for every monday of the current month then i create the sql statement and get the data but i'm stumped.
any further help would be much appreciated.

heres the vba code:
strSQL = "SELECT wkdaychrisbutler.emphrs.dte,wkdaychrisbutler.emphrs.emp,wkdaychrisbutler.emphrs.bs,wkdaychrisbutler.emphrs.hrs "
strSQL = strSQL & "FROM wkday "
strSQL = strSQL & "WHERE (wkday.emphrs.dte) Between " & lngFirstOfMonth & " And " & lngLastOfMonth & " ORDER BY "
strSQL = strSQL & "wkday.emphrs.dte;"
Debug.Print strSQL

 

[CJ_London]

can you show what you have in the wkday table and an example of what you are trying to get as a result, I really don't understand the fields you are using

 

[Rand]

CJ: basically i have a simple table of fields :
wkday is the query name heres the sql for it:
SELECT emphrs.*, emphrs.dte, emphrs.emp
FROM emphrs
WHERE (((emphrs.emp)=forms!CalendarAnnual!Text105.value) And ((Weekday([dte],1))=1) And ((Month([dte]))=Month(Now())));

emphrs is the table name
dte:date -ie 11/11/2013
emphrs:hours -ie 4
bsrder number - ie bs12345
emp:employee - ie fred

the query you helped me with sorts out only a given day each week during the current month. like only show mondays in current month.

i have a calendar which populates from the sql code. getting values from the query
which is:
strSQL = "SELECT wkday.emphrs.dte,wkday.emphrs.emp,wkday.emphrs.bs,wkday. emphrs.hrs "
strSQL = strSQL & "FROM wkday "
strSQL = strSQL & "WHERE (wkday.emphrs.dte) Between " & lngFirstOfMonth & " And " & lngLastOfMonth & " ORDER BY "
strSQL = strSQL & "wkday.emphrs.dte;"
Debug.Print strSQL
Set rstEvents = db.OpenRecordset(strSQL) fails here (with too few parameters 1)

note : lngFirstOfMonth is a serilized date so is lngLastOfMonth
but if i run the sql in a query no error comes up.
i know its due to the query has parameters from the where clauses.
for a fact if i change the sql to
SELECT emphrs.*, emphrs.dte, emphrs.emp From emphrs WHERE ((emphrs.emp)= "Fred astaire") And (Weekday([emphrs.dte], 1) = 1) And (Month([emphrs.dte])) = Month(Now());
i get an error 13 type mismatch but where is the mismatch
the emp name or is it the dates

 

[namliam]

Typicaly one wouldnt have any dots in any column names since a dot is used for the seperator of tables/queries and column names....

It looks to me like you are trying to use columns from the table/query/form called wkdaychrisbutler which isnt part of the query which would cause your problem in VBA

Also using the number substitutes for the dates may work (for now) but is only asking for trouble down the road. I can strongly advice you to use the proper date annotations
strSQL = strSQL & "WHERE (wkday.emphrs.dte) Between #" & DateInMM/DD/YYYYFormat & "# And #" & OtherDateInMM/DD/YYYY & "# ORDER BY "

Also using the strSQL = strSQL & to continue the lines is "subobtimal" instead try using line continuations when possible
strsql = "Select ... " & _
"From ....." & _
"Where ... .... etc.

Edit:
Dont think you can run queries thru VBA using a reference to a form (forms!CalendarAnnual!Text105.value)
Also try using "usefull" names for controls when possible to ease future maintenance.

 

[Rand]

the dots are there cause its based on a query i guess.
if i remove them it say 3265 item not found in this collection
if i run these sql statements in the query designer i get no errors and correct data.

 

[namliam]

running a query from the designer and running it from VBA isnt exacly the same thing...

The type mismatch is mostlikely the dates as you are using a long instead of a proper date.

Since you are not using multiple tables, you dont need to prefix your columns at all... if one of your columns is called dte, you can just do Select dte instead of any prefix.
If you prefix you generaly do so with only the query ... Never seen anyone do it with the table inside the query ....
Which should result in Select wkday.dte

 

[Rand]

ok found the param thats giving me the issue:
heres the statement
strSQL = "SELECT hrs, bs, dte, emp FROM emphrs WHERE (((emp)= 'Andy') AND ((Weekday([dte],1))=1) AND ((Month([dte]))=Month(Now())));"

its the name of the employee in single quotes which works but i have a field on the form that i can change the name in it. which is text105
how can code the srtring in place of the name in quotes

dim j as string
j = me.text105
how can i replace WHERE (((emp)= 'Andy') with the text thats in the textbox
i tried evrything like
WHERE (((emp)= '" & j & "'" )
once i can figure that out it will work.

 

[CJ_London]

Assuming your VBA is in the same form as text105 then use this

strSQL = "SELECT hrs, bs, dte, emp FROM emphrs WHERE emp= '" & Text105 & "' AND Weekday([dte[COLOR=red]],1[/COLOR][COLOR=red])=1[/COLOR] AND Month([dte])=Month(Now())"

Note also the bit highlighted in red - the ,1 is the default anyway so is not required and =1 means =Sunday - I thought you wanted Mondays? so should be =2

Also, just to be clear, I presume your table emphrs has a text field for the emp field and it is not using a lookup

 

[namliam]

(forms!CalendarAnnual!Text105.value)
Also try using "usefull" names for controls when possible to ease future maintenance.

can really strongly suggest you change the field name

 

[Rand]

Re: access 2010 Query dates [Solved]

It worked! thank you
I changed the day just to see if the records would change along with it.
I will take your advice on the naming conventions now that i know it can be done.
thank you again i have been messing around with this for a week now.
your awesome CJ