Sql Statement Help

[helpmeplease]

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?

[szlamany]

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...

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?

[helpmeplease]

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.

[szlamany]

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?

Post some code - what are you using?

[helpmeplease]

I'm not sure what you meant by ms sql.


gstrsql = "Select * FROM Payment Where Date Like '2%' and Like '%2005' "

[szlamany]

I'm not sure what you meant by ms sql.


gstrsql = "Select * FROM Payment Where Date Like '2%' and Like '%2005' "

Maybe something like:

gstrsql = "Select * From Payment Where [Date] >= #2004-01-01# and [Date] <= #2004-12-31#"

I believe that in ACCESS you have to delimit your dates with #-signs.

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:

gstrsql = "Select * From Payment Where DatePart(yyyy,[Date])=2004"

I'm putting [DATE] in []-brackets because I think that DATE is a reserved keyword - is that really you FIELD NAME for the DATE?

[helpmeplease]

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.

[szlamany]

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??

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.

[helpmeplease]

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.

[szlamany]

Then it should be something like:

strSql = "Select * From Payment" _
    & " Where DatePart(yyyy,[Date])=" & cboYear.Text _
    & " and DatePart(mm,[Date])=" & cboMonth.Text

Good luck.

[helpmeplease]

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

[szlamany]

No problem - glad it worked for you...