extract just the month or year from a record with date 4/5/00

[Bebe]

Can anyone help me with the code using VB6 and using SQL code to:

the user selects from a drop down box the month from one drop down box and the year from another drop down box

cboMonth
cboYear

cboMonth leaves you with text, example "January". I think I would then need to turn this into "1" somehow. the Year is formatted as 2000 but I need it to query the database as "00"

How do I take what the user selects in a form and query the database date for just the month and year; doing nothing with the day.

The report for example is run once a month. If they select March. They need to get a count of how many records were inputted for the month of March. I use the field Date_Opened to query this as it is a required field. So when they select March for the month and 2000 for the year or 00 I need it to go to the database select Date_Opened and look for all records beginning in that field with "3" and then query the year portion and find only records that are from the cboYear field. So although I have a day, I need to do nothing with that field.

Anybody have any good ideas on how I could do this with code or the SQL code to do this,

thanks in advance

[Mark Sreeves]

Try this....

Option Explicit

Private Sub Command1_Click()
RunSQL
End Sub

Private Sub Form_Load()
Dim i As Integer
For i = 1 To 12
  cboMonth.AddItem Format(i & "/" & i, "mmmm")
Next i

For i = 0 To 20
  cboYear.AddItem 1990 + i
Next i

End Sub
Private Sub RunSQL()
Dim strSQL As String



strSQL = "SELECT * From TableName WHERE ((Month(Date_Opened )=" & cboMonth.ListIndex + 1 & " And Year(Date_Opened)='" & cboYear.Text & "'));"

'now open your recordset...
'and get the record count

End Sub

[Steve Thomas]

I haven't tried this code but I think it wil get you moving in the right direction.

You could only display the month in your box but actually keep the full date. example:

cboMonth = format("date()", "mmm")

This will display only the month but will keep the full date.

Then when you want to convert it to a number you could format it for just the month using another format function.

Thats my suggestion.

Good luck

[bsmith]

Here is a select statement that will work.

select * from table_name where DatePart(year,date_field)=1999 and DatePart(month,date_field)=01

You will need to put your table name and variable names in. I am using SQL server. If you are using access there are similar functions that you can use to get a part of the date.

[Bebe]

Im trying some of these suggestions but in both the first and later cases I get Too few parameters.Expected 2.

Here is the code. I must be leaving something out of the code that is in VB quering an Access database using a databound control:

Data1.RecordSource = "SELECT * FROM MASTER WHERE DATEPART(year, DATE_OPENED)=00 AND DATEPART(month, DATE_OPENED)=3"

[Mark Sreeves]

datepart is used thus:

datepart("yyyy",now()) '=2000
datepart("m",now()) '= 4

take a look at the help files

[Bebe]

yes, but I need to use it with variables in an SQL statement so there's the trick for me.

I need to take a field where the user inputs a debt in text like January then it need to query a field called Date_Opened in the database for on the value of 3 and the date is formatted 3/4/00 in the database. I also need to get the year from the same field and return back a report that lists all the inputs for the month and year for Date_Opened field

thanks

[Steve Thomas]

You must use & statement when you want to use a variable:

Here's a very basic example.

Lets assume your Database has a table called "TABLE" and one field called "FIELD". If FIELD were an integer do this:

Dim strSQL as string
Dim "USERINPUT" as integer
USERINPUT = 3

strSQL = "Select * from TABLE where FIELD =" & USERINPUT


If FIELD were a string do this:

Dim strSQL as string
Dim "USERINPUT" as string

USERINPUT = "FRED" or whatever

strSQL = "Select * from TABLE where FIELD ='" & USERINPUT & "'".

Notice that for strings you must suply the ' around your variable.