SQL not working properly

[Malcy]

Hi
Can anyone tell me why this isn't working
_____________
' Set [bCalPh] to YES for the specified Public Holiday
strSQL = "UPDATE [tblCalendar] SET [tblCalendar].[bCalPh] = Yes " & _
"WHERE ([tblCalendar].[dtmCalDate])=#" & [Forms]![frmPublicHoliday].[txtHolDay] & "#;"
cnn.Execute strSQL

MsgBox "The public holiday has been set.", vbOKOnly, "System information"
_________________
It appears to go through without any error and the message box comes up but when I go to tblCalendar the field bCalPh has not been changed to yes for the date I entered on the form.
I wondered if it was to do with date format where I had long date in tblCalendar but short date on the form so changed tblCalendar to short date but still it doesn't work.
Any thoughts or help greatly appreciated.
One day I might get the hang of this SQL stuff, if I don't snuff it first!!
Best wishes

Malcy

 

[=TB=]

Is the field [bCalPh] a yes/no field? Then value for yes is -1 not YES

 

[Malcy]

Thanks =TB=
I tried changing Yes to -1 but still no difference.
Do I need to wrap the -1 up with some form of inverted commas?
I tried wrapping it as "-1" and it didn't like that!

Malcy

 

[Mile-O]

Try putting CDate() around [Forms]![frmPublicHoliday].[txtHolDay]

You may have a date format which is akin to a string and, therefore, the comparison between 19/04/04 and 19-Apr-2004, for example, is that they are not equal:

CDate([Forms]![frmPublicHoliday]![txtHolDay])

 

[Mile-O]

Try putting CDate() around [Forms]![frmPublicHoliday].[txtHolDay]

You may have a date format which is akin to a string and, therefore, the comparison between 19/04/04 and 19-Apr-2004, for example, is that they are not equal:


strSQL = "UPDATE [tblCalendar] SET [tblCalendar].[bCalPh] = True " & _
"WHERE ([tblCalendar].[dtmCalDate])=#" & CDate([Forms]![frmPublicHoliday]![txtHolDay]) & "#;"
cnn.Execute strSQL

 

[Malcy]

Thanks MIle for the CDate suggestion
I put that in and was testing and it seemed to be OK but in the testing I discovered what was happening.
When I entered 02/01/2005 as a public holiday on the form the SQL was processing for 01/02/2005 in tblCalendar
Since I was looking at 02/01/2005 I thought it hadn't done anything.
I assumed that since both were short dates I wouldn't have a problem but clearly SQL is using a US date format.
I think it was that rather than anything to do with using CDate().
Only problem is how do I get around it? Presumably I somehow have to incorporate Format #dd/mm/yyyy# into the textbox expression but not sure how.
Any help would be most appreciated!!
Thanks

Malcy

 

[EMP]

You can format the table field to match the entry in the text box e.g. if the entry in txtHolDay is 02/01/2005, you can use:-

"WHERE Format([tblCalendar].[dtmCalDate],'dd/mm/yyyy')='" & [Forms]![frmPublicHoliday].[txtHolDay] & "';"

 

[Malcy]

Thanks
I tried that and it didn't do as expected. Also tried one of Pat's suggestions on another thread of using DateValue but that didn't work either.
My ending code is

    Dim strSQL As String
    Dim cnn As ADODB.Connection
    Dim PhDate As Date
    
    Set cnn = CurrentProject.Connection
    
    PhDate = Me.txtHolDay
    
' Set [bCalPh] to YES for the specified Public Holiday
    strSQL = "UPDATE [tblCalendar] SET [tblCalendar].[bCalPh] = -1 " & _
             "WHERE Format([tblCalendar].[dtmCalDate], 'dd/mm/yyyy')= #" & DateValue(PhDate) & "#;"
    cnn.Execute strSQL
    
    MsgBox "The public holiday has been set.", vbOKOnly, "System information"
    DoCmd.Close
    
    Set cnn = Nothing

and I have used the ultimate cop out of putting a label on the form suggesting that the USA date format has to be used!!! Horrid I know but it will do till I find a solution - one day!
Thanks everyone for their help.
Best wishes

Malcy

 

[Jon K]

EMP's suggestion should work.

You have to replace the two # signs with two single quotes as shown in EMP's post since the Format() function returns a text string.

 

[Malcy]

Yep
Working now
Thanks a million
Best wishes

Malcy