Syntax 2

[goldygirl]

Here what I have……

mdHCQTD.CommandText = "select sum([IViami]) as Total from netsales where (((IVMKPL) IN('LA', 'PC')))" & _
"AND ihyear = " & Forms![DateRange]![Year] & _
"AND ihperd >= " & FromPeriod & _
"AND ihperd <= " & Forms![DateRange]![Period] & _
"AND cmcusn = " & txtCustNum

I am getting run-time error 424 (object Required). What I am missing????

 

[jksmi]

Hi,

Sounds like the command object hasn't been set up, but difficult to tell as this would be the preceding lines to the code you posted - have you set it up something like this...

dim cnn as new ADODB.Connection
Dim mdHCQTD As new ADODB.Command

set cnn.Open "YOUR_CONNECTION_STRING"    [COLOR=green]' or CurrentProject.Connection[/color]

Set mdHCQTD.ActiveConnection = cnn
mdHCQTD.CommandText = "text"

I know I've used early binding but I thought I'd keep the example brief.


HTH, Jamie
FAQ219-2884

 

[goldygirl]

Dear Jamie,
The command object set up OK. Here what I have:

Dim FromPeriod As Integer
Dim rstHCQTD As New ADODB.Recordset
Dim cmdHCQTD As New ADODB.Command
Set cmdHCQTD.ActiveConnection =
CurrentProject.Connection

If Forms!DateRange!Period = 3 Then
FromPeriod = 0
FromPeriod = Forms![DateRange]![Period]
FromPeriod = FromPeriod - 2
mdHCQTD.CommandText = "select sum([IViami]) as
Total from netsales
where (((IVMKPL)in ('PB', 'PC'))))" & _
"AND ihyear = " & Forms![DateRange]![Year] & _
"AND ihperd >= " & FromPeriod & _
"AND ihperd <= " & Forms![DateRange]![Period] & _
"AND cmcusn = " & txtCustNum
End If

 

[randy700]

SPACING.....

You have:
where (((IVMKPL) IN('LA', 'PC')))" & _
"AND ihyear = " & Forms![DateRange]![Year] & _
"AND ihperd >= " & FromPeriod & _
"AND ihperd <= " & Forms![DateRange]![Period] & _
"AND cmcusn = " & txtCustNum

This leaves no spaces between 'PC'))) and the 1st AND or between the ![year] and the 2nd AND etc. Put a space between the quotation marks and the word AND.

Additionally, if you are referring to dates, you need to include the #... example:
"AND ihperd >= # " & FromPeriod & "# " & _


Randy

 

[jksmi]

Hi,not sure if you've copied and pasted or retyped, but you've missed off the c for mdHCQTD.CommandText = "select sum([IViami]) as.... I assume that the form must be open as it got past the FromPeriod = Forms![DateRange]![Period]...

Are the year and period values valid? I just stuck a form together and used this exact code with the addition of the 'c' and a declared variable of txtCustNum and it worked fine so I'm thinking it must be something on the right hand side of the function - hard coding year, period and custno would be one way of making sure - put them back one at a time and you'll know which one is wrong...

HTH, Jamie
FAQ219-2884

 

[jksmi]

Hi, Randy's quite right about the spacing (I didn't actually test the query string as such) but I dont think that this is what's causing your error as it didn't cause me one (would have caused it when we tried to execute)

HTH, Jamie
FAQ219-2884

 

[goldygirl]

Hey guys,

I did changed the code (per Randy)...
mdHCQTD.CommandText = "select sum([IViami]) as Total from netsales where (((IVMKPL) IN('LA', 'PC')))" & _
" AND ihyear = " & Forms![DateRange]![Year] & _
" AND ihperd >= #" & FromPeriod & "#" & _
" AND ihperd <= #" & Forms![DateRange]![Period] & "#" & _
" AND cmcusn = " & txtCustNum

But I am still getting the same error msg....
Run-time error '424'
Object required ((((

Please HELP@@@@@

 

[jksmi]

Hi, I'm not sure if its a typo but your stil missing the 'c', it should be cmdHCQTD.CommandText = "select sum([IViami]) as Total from netsales where (((IVMKPL) IN('LA', 'PC')))" & _

HTH, Jamie
FAQ219-2884

 

[randy700]

How, exactly, are you trying to use this code?

Randy

 

[goldygirl]

Guys,

I just fing out that c in cmd is missing.......
Yahnoo )

Thanks a lot for your help)))

 

[jksmi]

Hi,

the way to avoid this, in future, is to declare Option Explicit at the top of the module - this means that you have to declare each variable you use and it will raise an error if you try to use a variable that hasn't been declared (which is effectively what has happened here). To save you typing this in each module you can go to Tools>Options>Editor (tab) and check Require Variable Declaration...

HTH, Jamie
FAQ219-2884