Retrieving a range of dates from the Database

[nkechifesie]

I am trying to retrieve dates from the database using a range.
This is the Code I am using which is giving me an error

Public AdVehDecp As ADODB.Connection
Public RsQry As ADODB.Recordset
Public StrSql As String
Set AdVehDecp = New ADODB.Connection
Set RsQry = New ADODB.Recordset
AdVehDecp.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\VEHICLE_TRACKER.mdb;"
AdVehDecp.Open
AdVehDecp.CursorLocation = adUseClient
StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND dtmVEHEXP <= #" & ExpDt & "#;"
    Set RsQry = AdVehDecp.Execute(StrSql)
 

the error occurs at Set RsQry = AdVehDecp.Execute(StrSql) saying
Run-time error '-2147217904(80040e10)';
No value given for one more required parameter

[hariharanmca]

I am trying to retrieve dates from the database using a range.
This is the Code I am using which is giving me an error

Public AdVehDecp As ADODB.Connection
Public RsQry As ADODB.Recordset
Public StrSql As String
Set AdVehDecp = New ADODB.Connection
Set RsQry = New ADODB.Recordset
AdVehDecp.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\VEHICLE_TRACKER.mdb;"
AdVehDecp.Open
AdVehDecp.CursorLocation = adUseClient
StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND dtmVEHEXP <= #" & ExpDt & "#;"
    Set RsQry = AdVehDecp.Execute(StrSql)
 

the error occurs at Set RsQry = AdVehDecp.Execute(StrSql) saying
Run-time error '-2147217904(80040e10)';
No value given for one more required parameter

Which datatype you are using (String or dateTime) for Date

[Killer42]

I am trying to retrieve dates from the database using a range.
This is the Code I am using which is giving me an error

Public AdVehDecp As ADODB.Connection
Public RsQry As ADODB.Recordset
Public StrSql As String
Set AdVehDecp = New ADODB.Connection
Set RsQry = New ADODB.Recordset
AdVehDecp.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\VEHICLE_TRACKER.mdb;"
AdVehDecp.Open
AdVehDecp.CursorLocation = adUseClient
StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND dtmVEHEXP <= #" & ExpDt & "#;"
    Set RsQry = AdVehDecp.Execute(StrSql)
 

the error occurs at Set RsQry = AdVehDecp.Execute(StrSql) saying
Run-time error '-2147217904(80040e10)';
No value given for one more required parameter

At a guess, you might need to enforce mm/dd/yyyy format on the dates you're inserting in the string. What is the data type of IssDt and ExpDt?

[hariharanmca]

At a guess, you might need to enforce mm/dd/yyyy format on the dates you're inserting in the string. What is the data type of IssDt and ExpDt?

No, In Format(value,"mm/dd/yyyy") mm->minute so we have to use Uppercase for Month like Format(dateValue,"MM/dd/yyyy")

[nkechifesie]

Which datatype you are using (String or dateTime) for Date

Its Datatime

[nkechifesie]

At a guess, you might need to enforce mm/dd/yyyy format on the dates you're inserting in the string. What is the data type of IssDt and ExpDt?

the dates have already been formated before being stored.
This is the code for IssDt and ExpDt

IssDt = Text3(0) + "/" + Text3(1) + "/" + Text3(2)
ExpDt = Text3(3) + "/" + Text3(4) + "/" + Text3(5)
IssDt = Format(IssDt, "DD/MM/YYYY")
ExpDt = Format(ExpDt, "DD/MM/YYYY")
 

[hariharanmca]

Its Datatime

Ok ,
then Just Try this

StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & Format(IssDt,"MM/dd/yyyy") & "# AND
dtmVEHEXP <= #" & Format(ExpDt,"MM/dd/yyyy") & "#;"
Set RsQry = AdVehDecp.Execute(StrSql)

[hariharanmca]

Ok ,
then Just Try this

StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & Format(IssDt,"MM/dd/yyyy") & "# AND
dtmVEHEXP <= #" & Format(ExpDt,"MM/dd/yyyy") & "#;"
Set RsQry = AdVehDecp.Execute(StrSql)

check IssDt and ExpDt are same data Type if it's Not then convert it
CDate(IssDt )

[Killer42]

check IssDt and ExpDt are same data Type if it's Not then convert it CDate(IssDt )

I think the critical point is simply that your dates must be formatted in U.S. format, with the month first (thanks for catching the minutes thing, hariharanmca).

So, if you are placing them in a string variable, you probably need to use MM/dd/yyyy format then. If it's a date variable, apply the format when inserting it into the SQL. Either way, SQL always expects American format in a #-delimited date literal.

[hariharanmca]

the dates have already been formated before being stored.
This is the code for IssDt and ExpDt

IssDt = Text3(0) + "/" + Text3(1) + "/" + Text3(2)
ExpDt = Text3(3) + "/" + Text3(4) + "/" + Text3(5)
IssDt = Format(IssDt, "DD/MM/YYYY")
ExpDt = Format(ExpDt, "DD/MM/YYYY")
 

in SQL Server you have to use like this

StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= ' " & Format(IssDt,"MM/dd/yyyy") & " ' AND
dtmVEHEXP <= ' " & Format(ExpDt,"MM/dd/yyyy") & " ' ;"
Set RsQry = AdVehDecp.Execute(StrSql)

in MS access can Execute


StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & Format(IssDt,"MM/dd/yyyy") & "# AND
dtmVEHEXP <= #" & Format(ExpDt,"MM/dd/yyyy") & "#;"
Set RsQry = AdVehDecp.Execute(StrSql)

[Killer42]

in SQL Server you have to use like this ...

Is the delimiter (# -vs- ') the only difference there?

Anyway, I found the thread I wanted to refer to earlier. See Literal DateTimes and Their Delimiters (#).

[hariharanmca]

Is the delimiter (# -vs- ') the only difference there?

Anyway, I found the thread I wanted to refer to earlier. See Literal DateTimes and Their Delimiters (#).

Yes Sql Server will Consider the date time format into string so we have to use - > '

in MSAccess will have Separate the date/Time and string so we have to use
- > #

[Killer42]

Yes Sql Server will Consider the date time format into string so we have to use - > '

in MSAccess will have Separate the date/Time and string so we have to use
- > #

Ok, thanks. I'll try to remember that. (I'll bet it's in that post from NeoPa that I referred to - I probably should read it. :o)

[nkechifesie]

[RESOLVED]
The issue has been resolved by changing

StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND 
dtmVEHEXP <= #" & ExpDt & "#;"
    Set RsQry = AdVehDecp.Execute(StrSql)
 

to

RsQry.Open "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND 
dtmVLEXP <= #" & ExpDt & "#;", AdVehDecp, adOpenDynamic, adLockPessimistic
 

[hariharanmca]

[RESOLVED]
The issue has been resolved by changing

StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND 
dtmVEHEXP <= #" & ExpDt & "#;"
    Set RsQry = AdVehDecp.Execute(StrSql)
 

to

RsQry.Open "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND 
dtmVLEXP <= #" & ExpDt & "#;", AdVehDecp, adOpenDynamic, adLockPessimistic
 

that's good,

this kind of problem is Difficult to answer if you don't send proper explanation

[Killer42]

The issue has been resolved by changing ...

So correct me if I'm wrong here - what changed was not the SQL or the date format, but going from Execute to Open? Just want to check that I'm clear on what the resolution was.