No value given for one or more required parameters

[DianeA]

I have this excel macro pulling information from a specific record in Access. on the "open.strQstr" stmt I get "No value given for one or more required parameters"

Is there something i am missing

thanks
diane
_______________________________________________

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim PullDate As Date
Dim CurrentValue As Date
Dim StrQstr As String


Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=G:\\Prices.mdb;"
Set rs = New ADODB.Recordset


CurrentValue = ActiveCell.Value


StrQstr = "SELECT * from tblTriggerPrices WHERE fldDate = CurrentValue"

With rs

.Open StrQstr, cn, adOpenKeyset, adLockOptimistic, adCmdText

Range("a5").Value = .Fields(onpeak)
Range("b5").Value = .Fields(offPeak)

End With

 

[jordanking]

try this:

StrQstr = "SELECT * from tblTriggerPrices WHERE fldDate = " & CurrentValue & ""

the current value is being evaluates as a string, not as a varibale.


.....
I'd rather be surfing

 

[DianeA]

This then gives me the following error:

Syntax error in number in query expression 'fldDate = 11.26.2007'

I have verified that a record does exist with this date in fldDate

But this does appear to be progress

Diane

 

[jordanking]

if the table's data type for the field is a date, then you need to add date qualifiers.


StrQstr = "SELECT * from tblTriggerPrices WHERE fldDate = #" & CurrentValue & "#"




.....
I'd rather be surfing

 

[DianeA]

Now I get this... and yes the flddate in the Table is configured as Date

Syntax error in number in query expression 'fldDate = #11.26.2007#'

 

[DianeA]

I've read somewhere that the "." between the date values is invalid and i must us "/"... could this be the case?

 

[DianeA]

Clarification... fldDate is the database field... and CurrentDate is the Excel value...

do I have these in the correct order and the # around the correct field?

 

[jordanking]

CurrentDate = ActiveCell.Value

StrQstr = "SELECT * from tblTriggerPrices WHERE fldDate = #" & CurrentDate & "#"

this should work, tested it locally on my machine.
The "." are what is breaking your code. You need to either change the way the date is entered in excel or capture the date value as a string and replace the . with / using the Replace() function. Then pass the string value between the ## as shown above.


ie.

dim CurrentDate as string
CurrentDate = cstr(ActiveCell.Value)
Replace(CurrentDate,".","/")
StrQstr = "SELECT * from tblTriggerPrices WHERE fldDate = " & CurrentDate & "#"

.....
I'd rather be surfing

 

[DianeA]

I'm getting a compile error on your Replace Statement. I added "As String" on end and now get "Compile Error: Statement invalid outside type block"


I first tried changing the Cell where the CurrentDate comes from, to a custom format of 11/28/2007 but it still shows 11.28.2007 in CurrentDate.

So I tried your method and got the compile error. Currently I have "Dim CurrentDate as Date" Is this still correct with your example or should i now change it to "as string"?

 

[DianeA]

ok... I have added

DIM newdt as string

newdt = Replace(CurrentDate,".","/")

StrQstr = "SELECT * from tblTriggerPrices WHERE fldDate = " & newdt& "#"

newdt does have 11/28/2007 but still get "Syntax error in FROM clause"

I also changed
Open StrQstr, cn, adOpenKeyset, adLockOptimistic, adCmdText
to
Open StrQstr, cn, adOpenKeyset, adLockOptimistic, adCmdTable

not sure which is better since i am just extracting values from the table and dumping onto the spreadsheet. No updates to the database will occur.

 

[jordanking]

sorry, if you use the second example i forgot two parts; a #, and replace needs to be evaluated to something.

use the following

dim CurrentDate as string
CurrentDate = cstr(ActiveCell.Value)
CurrentDate = Replace(CurrentDate,".","/")
StrQstr = "SELECT * from tblTriggerPrices WHERE fldDate = #" & CurrentDate & "#"

Yes you must declare the CurrentDate as a string.

as for your connection string, look at the MSDN locktype and cursorType. If you are only reading from the records in a forward direction without updateing the recordset, use the following, for better performance.

Open StrQstr, cn, adOpenForwardOnly, adLockReadOnly

If you need to update the recordset, then use your previous version with: adOpenKeyset, adLockOptimistic



.....
I'd rather be surfing

 

[DianeA]

Okay... i changed the adcmdTable back to adCmdText and we have got rid of the error. I need to understand these parameters a bit better.

now i get "Item cannot be found in the collection to the requested name or ordinal" on the following stmt

Range(TargetCellC).Value = .Fields(onpeak)

hmmm

 

[PHV]

StrQstr = "SELECT * FROM tblTriggerPrices WHERE fldDate = #" & newdt & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DianeA]

I got it... "onpeak" ..

thanks so much for your help...

 

[jordanking]

glad to help


.....
I'd rather be surfing

 

[SBerthold]

DianeA

Just replacing the dots with "/" will not be independent of the local/user date settings.

You have two problems here:

1. The user may use a completely different char as a date seperator.

2. Though the date seperators may be "." and changed to "/", what will happen if the user sets the date format in the country settings to dd.mm.yyyy as in 12.10.2008?

After replacing the dots with "/", it will get interpeted by JET as 10 December and not 12 October, (as entered by the user).

Use this to format the date, as it will be better prepared to be local and user date settings independent:

Format$(newdt, "\#yyyy-mm-dd\#")

(The "#" date identifier can also be substituted for a variable to make easier to be dbms independent)