Assign a date into SQL statement

[Ben_Entrew]

Hi all,

I want to create a table via SQL. This table should contain records that
begin after a specified date.

Something is wrong with this syntax.

Can someone please help me out here?

Thanks in advance.
Regards,
Ben

Sub TEST()

Dim t As Date

t = 1 / 3 / 2014

DoCmd.RunSQL "SELECT TRP.Customer, TRP.Material, TRP.Product_Class, TRP.TRP as Price, TRP.Valid_from, " & _
             " TRP.Valid_to INTO [New_Prices] " & _
             " FROM TRP " & _
             " WHERE (((TRP.Customer)= 1223) AND ((TRP.Valid_from)>#t#))"

End Sub

 

[Ben_Entrew]

Acutally I got a table TRP with a date column Valid_from, containing records with e.g.

Valid_from = 02.01.2014 (for 2nd Jan 2014).

I want to decide the user which date he wants to see, so I need a date variable t for example.

How can I select for these records in SQL ?

Thanks in advance.

 

[namliam]

Keep in mind in code dates are ALWAYS in US date format, MM/DD/YYYY which would make your date Feb/01/2014, not 2/Jan/2014

Check this thread:
http://www.access-programmers.co.uk/forums/showthread.php?p=1300908#post1300908

That should help you, if you have more questions post back here.

 

[Ben_Entrew]

Thank you so far Namliam,

I've read your suggested thread and tried this:

Dim t As Date
t = 1 / 2 / 2014

DoCmd.RunSQL "SELECT TRP.Customer, TRP.Material, TRP.Product_Class, TRP.TRP as TRP_in_EUR, TRP.Valid_from, " & _
             " TRP.Valid_to INTO [New_TRP_Witten] " & _
             " FROM TRP " & _
             " Where TRP.[Valid_from]  =" & "#" & t & "#"

What I don't understand : If I type in
...WHERE TRP.[Valid_from] = #1/2/2014#"

it works out. I'm gettin desperate here.

First I understood that I have to format my original Valid_from Dates into US Date format.
Second somehow I have to select a date in SQL.

How can I realize that?

 

[pr2-eugin]

Try,

Dim t As Date
t = DateSerial(2014, 2, 1)
[COLOR=Green]'t = CDate("2-Jan-2014")
't = #1/2/2014#[/COLOR]

DoCmd.RunSQL "SELECT TRP.Customer, TRP.Material, TRP.Product_Class, TRP.TRP as TRP_in_EUR, TRP.Valid_from, " & _
             " TRP.Valid_to INTO [New_TRP_Witten] " & _
             " FROM TRP " & _
             " Where TRP.[Valid_from]  = " & Format(t, "\#mm\/dd\/yyyy\#")

 

[namliam]

What Paul suggested use Dateserial or either of the commented versions...
NOTICE that the date entered without any function is enclosed by #date#

As for the format in the SQL there are a number of ways to do that as well, depending on your preference

" Where TRP.[Valid_from]  = " & Format(t, "\#mm\/dd\/yyyy\#")
" Where TRP.[Valid_from]  = #" & Format(t, "mm\/dd\/yyyy") & "#"
[COLOR="Red"]" Where TRP.[Valid_from]  = " & "#" & Format(t, "mm\/dd\/yyyy") & "#"[/COLOR]
" Where TRP.[Valid_from]  = Dateserial(" & Format(t, "YYYY,MM,DD") & ")"

The line in red is rarely used, I used it in the other thread to emphasize the need for the # around the date... You are obviously concatting two strings with is usually "not done"

 

[Ben_Entrew]

Thanks guys,
it works out with

t = #1/2/2014#

and Where TRP.[Valid_from] > " & Format(t, "\#mm\/dd\/yyyy\#")


I tried to assign a date to a variable startdate given by the user as 1/2/2014,

somehow it doesn't accept this startdate later in this subroutine.

Any idea how I can resolve this?

 

[namliam]

Presumably the given "date" is actually a string, strings cant be formatted....

Try it with the # inside the query string instead of the format string.

 

[Ben_Entrew]

Namliam,

I defined the variable startdate as Date.

startdate = 1/3/2014

I don't understand your comment inserting the # within the query. What do you mean with that?

 

[jdraw]

Datetype variables must be enclosed in #

startdate =#1/3/2014#

text variables in " eg mystring ="myvalue"

no enclosing characters for numeric variables.

Good luck

 

[Ben_Entrew]

Hello JD,

thanks for your hint.

My issue is when the user types in the date 01/03/2014 in the InputBox and I assign this to the date variable startdate = Inputbox.

How can I use this startdate later in the SQL Query?

 

[pr2-eugin]

Just use..

Dim startDate As Date
startDate = [URL="http://ss64.com/access/cvdate.html"]CVDate[/URL](InputBox("Please enter the Start Date", "Input Required", Date()))

DoCmd.RunSQL "SELECT TRP.Customer, TRP.Material, TRP.Product_Class, TRP.TRP as TRP_in_EUR, TRP.Valid_from, " & _
             " TRP.Valid_to INTO [New_TRP_Witten] " & _
             " FROM TRP " & _
             " Where TRP.[Valid_from]  = " & Format(startDate, "\#mm\/dd\/yyyy\#")

 

[Ben_Entrew]

Thanks Paul,

this was the solution.

You rescued my day.

Thanks.

 

[namliam]

as per paul's comment, an inputbox returns a STRING, as I suggested in the first place

 

[namliam]

And to be honest, you would be far better off instead of using Inputbox to make your own popup form with a datepicker on it so you can control (and inspect) your user's input much better.