Web Development



I have a problem in a Query

By Fawad0179 ·
I have a problem with the below query. It gives me the error in Date format. Means query produce syntax error.

sql = "select ms.Match_Number,ms.Match_date_Time from Season s,leauge l,Match_Schedule ms where s.Season_Id=ms.Season_Id and s.Season_Year=" & cmb_Season_Year.Text & " and l.leauge_Id=ms.Leauge_Id and l.Leauge_Name='" & cmb_Leauge_Name.Text & "' and ms.Match_Date_Time=#" & Format(cmb_Date_Time.Text, "DD.MM.YYYY" & " " & "hh:mm:ss") & "# order by ms.Match_Number"

Can you please help me in this regard. I shall be very thankful to you.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Go aften!

by TobiF In reply to I have a problem in a Que ...

If I understand this correctly, you are trying to use a SQL statement to retrieve data into an Excel sheet?

The VBA statement "FORMAT" needs an Excel time value as the input, which is floating point value, where the integer part indicates the date, and the decimal part is converted into time of day.

However, most probably, you get the date & time in a different format from the database.

Check what format you get from the database.

If you need to do calculations on this value, then it should be converted into an Excel date&time value. Otherwise, if the format is ok, just keep it.

If you need to edit the format, then that may be done in the sql statement (depending on sql version), or through VBA string manipulation. Post a couple of date&time from the database and tell us what you want to achieve.

Collapse -


by Tony Hopkinson In reply to I have a problem in a Que ...

is that whatever database you are using, (access?) doesn't like the date format, or whatever language this is doesn't like what you've put in the Format command?

Given Cmb_Date_Time is a string in order to change the date format , you'd first convert it back to a date, then back to a string in a different one.

There is a universal format for dates as far as SQL is concerned and it's "YYYY-MM-DD HH:MM:SS", doesn;t matter waht your (or a server's regional settings are that one will work. You can wrap it in # or double quotes.

If this was VBA or similar I would have expected that bit of the code to be

Format(CDate(cmb_date_Time.Text),"YYY-MM-DD HH:mm:ss")

If it already has a date in it though, why bother converting it at all?

PS do not use this technique in a serious program if any user input is involved, you should use parameterised queries, otherwise you are begging for a SQL injection attack.

Related Discussions

Related Forums