Printable View
I need to select back a months data from an access database where the date is a certain month and a certain year. The date is date/time datatype and is in US format in the database.I was thinking I'd be able to use some wildcards but not sure how to manipulate these in my where clause!
Any ideas?
We do MS SQL here - not ACCESS...Quote:
Originally Posted by helpmeplease
But have you looked into "WHERE DATEPART(yyyy,DATEFIELD)=2005 and DATEPART(mm,DATEFIELD)=2" for selectiong February 2005 entries?
You can also do WHERE DATEFIELD>='2005-02-01' and DATEFIELD<'2005-03-01' - although I believe in ACCESS the delimiter for a date is #-sign - right?
Well it's my query in vb i'm trying to write
I was thinking of something like :
Where date like '2%' and like '%2005' but not sure how to write it.
What do you mean - my query in VB?Quote:
Originally Posted by helpmeplease
Post some code - what are you using?
I'm not sure what you meant by ms sql.
gstrsql = "Select * FROM Payment Where Date Like '2%' and Like '%2005' "
Maybe something like:Quote:
Originally Posted by helpmeplease
I believe that in ACCESS you have to delimit your dates with #-signs.Code:gstrsql = "Select * From Payment Where [Date] >= #2004-01-01# and [Date] <= #2004-12-31#"
We use MS SQL SERVER 2000 - another database product from MS - it's syntax a bit different.
With MS SQL SERVER we would have used:
I'm putting [DATE] in []-brackets because I think that DATE is a reserved keyword - is that really you FIELD NAME for the DATE?Code:gstrsql = "Select * From Payment Where DatePart(yyyy,[Date])=2004"
The month could be any month so the number of days will be different depending on the month selected so that's why i was thinking of where the date like 2(ie. february) with more data after it and ends with 2005 with data before the 2005.In other words 02... /??/... 2005
Dunno if I'm explaining it very well.
I don't want to hard code the month and year.they are selected through comboboxes and assigned to variables.
Does ACCESS have a DATEPART function??Quote:
Originally Posted by helpmeplease
If so, then ask for the YEAR and then the MONTH and build the SQL string like I suggested.
I never said to hardcode.
Post some actual code from your app - so we can work on that.
Yeah Access has a DATEPART function so i'll give that a shot and see how that goes.
all i'm pretty much doing is getting the month value 1-12 from combobox and year and assigning these to a variable and hoping to utilise them someway to get specific data from Payment table.
There isn't much code to show.
Then it should be something like:
Good luck.Code:strSql = "Select * From Payment" _
& " Where DatePart(yyyy,[Date])=" & cboYear.Text _
& " and DatePart(mm,[Date])=" & cboMonth.Text
gstrSql = "SELECT * From Payment " _
& "WHERE DatePart('yyyy',[Date_Received]) = " & cboYear.List(cboYear.ListIndex) _
& " AND DatePart('m',[Date_Received]) =" & (cboMonth.ListIndex + 1)
This works
Cheers for the help
No problem - glad it worked for you...