Date conversion(?) as part of sql query

[StlMacMan]

I have a VB6 form with bound ADODB dc which needs to query (adcmdText sql SELECT)an Access 2002 db table date field in order to match a user input textbox entry (TxtCtrtYear) on the form and then display matches. The Access date field (Start_Date) is formatted as short date and so is the display text box. Both of these need to remain in that format. I can't figure out how to extract the year value from the date field in order to make the query work. I'd appreciate any help w/ the syntax. Thanks. --Ed

Here's my query code:

SELECT * FROM Contract WHERE Start_Date = '" & TxtCtrtYear.Text & "'

 

[jebenson]

You can simply use the Year() SQL function:

SELECT * FROM Contract WHERE Year(Start_Date) = " & TxtCtrtYear.Text

Note that there are no single quotes around the value from TxtCtrtYear.Text, beacuse the Year() function returns a numeric value.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[StlMacMan]

Jebenson, thanks for the reply. I had to remove your " in order to get it to take it as sql. Here's the code.

Private Sub Form_Load()

AdoGroupYearSearch.CommandType = adCmdText
AdoGroupYearSearch.RecordSource = "SELECT * FROM Contract WHERE Year(Start_Date) = & Form3.txtCtrtYear.Text"

AdoGroupYearSearch.Recordset.Requery
AdoGroupYearSearch.Refresh

End Sub

Object variable or With block variable not set. Debug highlights AdoGroupYearSearch.Recordset.Requery

This is my third use of this code in this program. The other two work fine, but do not contain dates, so I suspect syntax here. What your suggestion produces as shown by the "tool tip" (if that is what it is called) mouse over the sql statement is "AdoGroupYearSearch.RecordSource = "SELECT * FROM Contract WHERE Year(Start_Date) = 2004" which is seems what should be passed to the db, but must not be since the error message persists. What do you think? --Ed

 

[NJLDoc]

The quotation mark removed was necessary because the sample given was only partial. The code shold be:

"SELECT * FROM Contract WHERE Year(Start_Date) =" & Form3.txtCtrtYear.Text & ";"

 

[StlMacMan]

Doc--I still get the same error message: Object variable or With block variable not set. When I hit Debug it highlights AdoGroupYearSearch.Recordset.Requery

When I worked out the other two, this appeared to be caused by the RecordSource property of the ADODB dc being out of sync w/ the code shown earlier. But I have synced them and still no go. So, I suspect the syntax for getting the year value out of the db. What do you think? --Ed

 

[NJLDoc]

Can you post the entire procedure. I think there may be a declaration or set statement missing?

 

[StlMacMan]

Here it is. The bulk of the work is done by the ADO bound data control.

Private Sub Form_Load()

AdoGroupYearSearch.CommandType = adCmdText
AdoGroupYearSearch.RecordSource = "SELECT * FROM Contract WHERE Year(Start_Date) = " & Form3.txtCtrtYear.Text & ""

AdoGroupYearSearch.Recordset.Requery
AdoGroupYearSearch.Refresh

End Sub

 

[jebenson]

This can sometimes happen when there is no RecordSource set in the Properties dialog of the ADODC. Try right-clicking on the ADODC and select 'ADODC Properties'. In Properties select the Record Source tab, select adCmdText as the Command Type, and put this for the Command Text (SQL):

SELECT * FROM Contract WHERE 1=0

This query will return no records, but having it there will hopefully make that error message go away.

Let me know if this works out.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[StlMacMan]

Jebenson--

The "1=0" attempt didn't help. Same error message. I can't figure out why this is so hard! The text box entry is just "general" format and the user enters e.g., 2004

I am using the sql query below and entered it into the RecordSource property of the ADO dc as well. No luck. Very frustrating. What do you think? The "Start_Date" db field is an Access Date. Obviously I'm trying to strip the year from the db field and use it as the select to match with the user input in order to produce the record set for the bound dc. I gave up trying to do this with unbound data because I couldn't figure out how to make step-through controls, but don't see how to fix this bound control element. What do you think. --Ed

"SELECT * FROM Contract WHERE Year(Start_Date)=" & Form3.txtCtrtYear.Text & ";

 

[StlMacMan]

Figured it out. The correct sql is:
"SELECT * FROM Contract " _
& "WHERE year(Start_Date) = " & Form3.TxtCtrtYear.Text & ""

Part of the issue was my failure to reselect the ADO DC on the form after I had copied the main form fields from an earlier form. Doh! Thanks to all who responded. --Ed