What is wrong with this statment: a variable within a sql statment

[MsMope]

Hi, this is written in VBScript, but the query values are not working, I am still retrieving a years worth of data instead of just the data I am looking for.

Query.AdditionalCriteria = "Date_Shipped >='" & dStartDate &"'"
Query.AdditionalCriteria = "Date_Shipped <='" & dEndDate & "'"

 

[SkipVought]

Ms.

Dates are NOT strings. remove the tics

Query.AdditionalCriteria = "Date_Shipped >=" & dStartDate & ""
Query.AdditionalCriteria = "Date_Shipped <=" & dEndDate & ""

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[MsMope]

Error on line 11:
Unterminated string constant

that is the message I get when I remove the tic's, any suggestions?

 

[SkipVought]

How are dStartDate/dEndDate defined?

How were they assigned a value?

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[RoyVidar]

In what context is this used?

For ordinary Access queries, hash (#) is the delimiter, for SQL server/MSDE and probably others, single quotes are delimiters, but it would depend a little on where/how it is used.

Relating to locale, one may perhaps need to format the dates, if ones date format differs from the US date format

[tt]..."Date_Shipped <=#" & format$(dEndDate,"yyyy-mm-dd") & "#"[/tt]

Roy-Vidar

 

[MsMope]

Skip,
Here is the code for the other script that I am asking for a date on:

'Prompt the user to input a value
dStartDate = InputBox("Please enter a start date.")
dEndDate = InputBox("Please enter an end date.")

'Test the values to make sure that they are both dates.
'If not, cancel the integration
If Not IsDate(dStartDate) then
MsgBox("You have not entered a valid date. " & _
"Please start the integration again.")
CancelIntegration "Invalid date entered."
Exit Sub
End If

If Not IsDate(dEndDate) then
MsgBox("You have not entered a valid date. " & _
"Please start the integration again.")
CancelIntegration "Invalid date entered."
Exit Sub
End If

'Set the resulting values as global variables that can be
'retrieved in the "Before Query" script
SetVariable "dStartDate", dStartDate
SetVariable "dEndDate", dEndDate

here is the other script, based on a field, I was able to run without tic's but I do not recieve any results. FYI: I omitted a set of "" and that is why I recieved the error that I posted before:

'Get the start date and end date, which were stored in
'global variables in the "Before Integration" script
dStartDate = GetVariable("dStartDate")
dEndDate = GetVariable("dEndDate")

'Set the additional query criteria
Query.AdditionalCriteria = "Date_Shipped >=" & dStartDate &""
Query.AdditionalCriteria = "Date_Shipped <=" & dEndDate & ""

 

[SkipVought]

Your "Dates" (dStartDate/dEndDate) are really NOT Dates by rather TEXT.

As Roy suggested, when using a TEXT STRING in a Date Expression, delimit with #...

Query.AdditionalCriteria = "Date_Shipped >=#" & dStartDate &"#"
Query.AdditionalCriteria = "Date_Shipped <=#" & dEndDate & "#"

BTW, Real Dates are really NUMBERS, like today is 38271. What you and I like to SEE as a date is a FORMAT of that kind of NUMBER.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[MsMope]

OK so I did as you both have suggested, using the #, I retrieve 0 records, which is incorrect. Now what?

 

[PHV]

In access, the literal date must be formatted in an unambigous way, like #yyyy-mm-dd#, or in US format, ie #mm/dd/yyyy#.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Is your query ONLY using date as a criteria?

What records do you anticipate retrieving with what input dates?

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[MsMope]

The code that I posted previously is all that is behind my integration for vbscript. I am anticipating pulling in order information who's shipped date is between those two values.

 

[MsMope]

ERROR: The query could not be executed.
The following message was returned by ODBC:

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '#'.

 

[RoyVidar]

From my above "for SQL server/MSDE and probably others, single quotes are delimiters, but it would depend a little on where/how it is used."

You are doing something with SQL server (which you didn't tell) in a language that doesn't resemble VBA much, why is this in the Access VBA forum???

Check out faq222-2244 #3 (though the whole faq is worth a read) I believe vbscript is forum329. There are also SQL server fora.

Roy-Vidar

 

[PHV]

In SQL Server the syntax for date literal is:
'yyyy-mm-dd'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244