Problems when executing SQL Where statement with a date

Question

When I hardcode the values and execute the SQL statement within Access 2010, the command is executed and the values required are returned.

SELECT [Event ID], [Crowd Size], [Event Name] FROM Event WHERE ([Event Date]=#8/11/2012#) AND ([Event Name]="Peter")

However when I use text boxes in place of the hardcoded values

("SELECT [Event ID], [Crowd Size], [Event Name] FROM [Event] WHERE ([Event Name]='" + textBoxEventName.Text + "') AND ([Event Date]=#" + textBoxEventDate.Text + "#)");

When trying to read from the datareader, it is unable to do so as there doesn't seem to be any values in there.

I have gone through the code in debug mode and the values in the text boxes are correct, but still doesn't happen.

Try assigning your sql string to a text variable and check using the debugger that it is as you expect. — adrianbanks
– adrianbanks, Commented Feb 22, 2012 at 23:21
This has nothing to do with Access since this is before it hits the database. This is going from VS form to whatever function is generating the sql. May want to step through to see what's going on. Have you tried textboxEventDate.Text.ToString("formathere")? — sam yi
– sam yi, Commented Feb 22, 2012 at 23:28
@adrianbanks I have assigned the textbox contents to a string variable, the debug mode shows the values as it should, but it still doesn't seem to be working. — Peter Roche
– Peter Roche, Commented Feb 22, 2012 at 23:36

NicoTek · Accepted Answer · 2012-02-23 00:17:29Z

1

Access is a bit tricky with the SQL Queries that it runs...

Make sure that the date you are grabbing is in the correct format, or assign it a format your-self.

http://msdn.microsoft.com/en-us/library/az4se3k1.aspx (Standard Date and Time Format Strings)

also make sure that there are all the quotation marks necessary and that you are not using any of the "reserved words"

http://support.microsoft.com/kb/286335 (List of reserved words in Access)

answered Feb 23, 2012 at 0:17

NicoTek

1,1671 gold badge14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ChrisPadgham Over a year ago

[Event Date] is a Date/Time field presumably. Check that it has nothing to do with the time component.

Armaan · Accepted Answer · 2012-02-23 07:53:28Z

0

Try this: see this page: http://www.tizag.com/sqlTutorial/sqlselect.php

answered Feb 23, 2012 at 7:53

Armaan

19011 bronze badges

Comments

Peter Roche · Accepted Answer · 2012-02-23 14:31:56Z

0

After many hours of fiddling around with this problem,

The solution is that Access SQL likes dates in the format

#YYYY/MM/DD#

answered Feb 23, 2012 at 14:31

Peter Roche

3452 gold badges6 silver badges18 bronze badges

Collectives™ on Stack Overflow

Problems when executing SQL Where statement with a date

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related