By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,611 Members | 1,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,611 IT Pros & Developers. It's quick & easy.

Retrieving a range of dates from the Database

P: 62
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
Expand|Select|Wrap|Line Numbers
  1. Public AdVehDecp As ADODB.Connection
  2. Public RsQry As ADODB.Recordset
  3. Public StrSql As String
  4. Set AdVehDecp = New ADODB.Connection
  5. Set RsQry = New ADODB.Recordset
  6. AdVehDecp.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\VEHICLE_TRACKER.mdb;"
  7. AdVehDecp.Open
  8. AdVehDecp.CursorLocation = adUseClient
  9. StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND dtmVEHEXP <= #" & ExpDt & "#;"
  10.     Set RsQry = AdVehDecp.Execute(StrSql)
  11.  
the error occurs at Set RsQry = AdVehDecp.Execute(StrSql) saying
Run-time error '-2147217904(80040e10)';
No value given for one more required parameter
Jan 4 '07 #1
Share this Question
Share on Google+
15 Replies


hariharanmca
100+
P: 1,977
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
Expand|Select|Wrap|Line Numbers
  1. Public AdVehDecp As ADODB.Connection
  2. Public RsQry As ADODB.Recordset
  3. Public StrSql As String
  4. Set AdVehDecp = New ADODB.Connection
  5. Set RsQry = New ADODB.Recordset
  6. AdVehDecp.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\VEHICLE_TRACKER.mdb;"
  7. AdVehDecp.Open
  8. AdVehDecp.CursorLocation = adUseClient
  9. StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND dtmVEHEXP <= #" & ExpDt & "#;"
  10.     Set RsQry = AdVehDecp.Execute(StrSql)
  11.  
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
Jan 4 '07 #2

Expert 5K+
P: 8,434
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
Expand|Select|Wrap|Line Numbers
  1. Public AdVehDecp As ADODB.Connection
  2. Public RsQry As ADODB.Recordset
  3. Public StrSql As String
  4. Set AdVehDecp = New ADODB.Connection
  5. Set RsQry = New ADODB.Recordset
  6. AdVehDecp.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\VEHICLE_TRACKER.mdb;"
  7. AdVehDecp.Open
  8. AdVehDecp.CursorLocation = adUseClient
  9. StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND dtmVEHEXP <= #" & ExpDt & "#;"
  10.     Set RsQry = AdVehDecp.Execute(StrSql)
  11.  
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?
Jan 4 '07 #3

hariharanmca
100+
P: 1,977
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")
Jan 4 '07 #4

P: 62
Which datatype you are using (String or dateTime) for Date
Its Datatime
Jan 4 '07 #5

P: 62
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
Expand|Select|Wrap|Line Numbers
  1. IssDt = Text3(0) + "/" + Text3(1) + "/" + Text3(2)
  2. ExpDt = Text3(3) + "/" + Text3(4) + "/" + Text3(5)
  3. IssDt = Format(IssDt, "DD/MM/YYYY")
  4. ExpDt = Format(ExpDt, "DD/MM/YYYY")
  5.  
Jan 4 '07 #6

hariharanmca
100+
P: 1,977
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)
Jan 4 '07 #7

hariharanmca
100+
P: 1,977
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 )
Jan 4 '07 #8

Expert 5K+
P: 8,434
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.
Jan 4 '07 #9

hariharanmca
100+
P: 1,977
the dates have already been formated before being stored.
This is the code for IssDt and ExpDt
Expand|Select|Wrap|Line Numbers
  1. IssDt = Text3(0) + "/" + Text3(1) + "/" + Text3(2)
  2. ExpDt = Text3(3) + "/" + Text3(4) + "/" + Text3(5)
  3. IssDt = Format(IssDt, "DD/MM/YYYY")
  4. ExpDt = Format(ExpDt, "DD/MM/YYYY")
  5.  

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)
Jan 4 '07 #10

Expert 5K+
P: 8,434
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 (#).
Jan 4 '07 #11

hariharanmca
100+
P: 1,977
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
- > #
Jan 4 '07 #12

Expert 5K+
P: 8,434
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)
Jan 4 '07 #13

P: 62
[RESOLVED]
The issue has been resolved by changing
Expand|Select|Wrap|Line Numbers
  1. StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND 
  2. dtmVEHEXP <= #" & ExpDt & "#;"
  3.     Set RsQry = AdVehDecp.Execute(StrSql)
  4.  
to

Expand|Select|Wrap|Line Numbers
  1. RsQry.Open "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND 
  2. dtmVLEXP <= #" & ExpDt & "#;", AdVehDecp, adOpenDynamic, adLockPessimistic
  3.  
Jan 4 '07 #14

hariharanmca
100+
P: 1,977
[RESOLVED]
The issue has been resolved by changing
Expand|Select|Wrap|Line Numbers
  1. StrSql = "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND 
  2. dtmVEHEXP <= #" & ExpDt & "#;"
  3.     Set RsQry = AdVehDecp.Execute(StrSql)
  4.  
to

Expand|Select|Wrap|Line Numbers
  1. RsQry.Open "SELECT txtVEHNO, dtmVLEXP FROM tblVEHLIC WHERE dtmVLEXP >= #" & IssDt & "# AND 
  2. dtmVLEXP <= #" & ExpDt & "#;", AdVehDecp, adOpenDynamic, adLockPessimistic
  3.  

that's good,

this kind of problem is Difficult to answer if you don't send proper explanation
Jan 4 '07 #15

Expert 5K+
P: 8,434
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.
Jan 4 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.