Incorrect syntax near keyword 'and'

The following code runs ok in an Access 2002 adp file connected to an SQL 2000 DB but gives an error incorrect syntax near keyword 'and' when I open with Access 2003 connected to SQL 2005.

Private Sub Form_Current()
With Forms!MonthSummary.Form
(this line is the error)    .Admin = DLookup("AdminPerc", "MemberDetail", "AccountID = " & AccountID & " and Period = " & Period)

Any ideas why?

Steve
smagaricAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>incorrect syntax near keyword 'and'

try check and make sure AccountID returns a numeric value ?
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
as well as Period...
0
smagaricAuthor Commented:
AccountID and Period are of type int in the SQL db.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

peter57rCommented:
Hi smagaric,
 Forms!MonthSummary.Form.Admin
is not valid syntax

Forms!MonthSummary.Admin
or
Me.admin


Pete
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>AccountID and Period are of type int in the SQL db.
ok, but since you're passing variables there, as what i mentioned, make sure variable AccountID and Period in your codes there returns numeric value.
0
GRayLCommented:
Try changing this line:

(this line is the error)    .Admin = DLookup("AdminPerc", "MemberDetail", "AccountID = " & AccountID & " and Period = " & Period)

To:

(this line is the error)    !Admin = DLookup("AdminPerc", "MemberDetail", "AccountID = " & AccountID & " and Period = " & Period)
0
GRayLCommented:
0
smagaricAuthor Commented:
Thanks guys but I'm not an Access programmer, I just got dumped with this problem. I'm sure it's something to do with the 'period'. Obviously Access 2003 works different to Access 2002. Or maybe it's SQL 2000 and SQL 2005 that is the problem. This is a bit too complicated for me. The strange thing is that this is the only form out about 20 in the project that doesn't work and the referencing is the same on all forms throughout the project. Thanks for your help.
0
GRayLCommented:
If the control Admin is a subform on Forms!MonthSummary then the correct syntax for it would be as I posted previously.  Can you elaborate?

0
smagaricAuthor Commented:
I tried all the possible ways to reference the forms as per all the comments but it still don't work. It still complaining about the incrrect syntax near 'and' I think I'll just have to run the app under Access 2002. It's just annoying that I can't figure it out. :-( I'll try upgrading the old Access 2002 to 2003 and see if I can reproduce the error.

Steve
0
GRayLCommented:
Can you confirm that 'Admin' is a subform on the MonthSummary form.
0
smagaricAuthor Commented:
I don't think .admin is a form. Here's the while routine below. Maybe this will make more sense.

Private Sub Form_Current()
With Forms!MonthSummary.Form
    .Admin = DLookup("AdminPerc", "MemberDetail", "AccountID = " & AccountID & " and Period = " & Period)
    .Referral = DLookup("ReferralPerc", "MemberDetail", "AccountID = " & AccountID & " and Period = " & Period)

ReferralOnly = DLookup("ReferralOnly", "MemberAccounts", "AccountID = " & AccountID)
    .lblReferral.Visible = ReferralOnly
    .MonthSummaryCredits.Enabled = Not ReferralOnly
    .MonthSummaryDebits.Enabled = Not ReferralOnly

End With
   
End Sub

Steve
0
GRayLCommented:
When you open the form in design view, right click and select properties. Now select the various controls on the form and you will see the properties change.  Select the Other tab in the properties window and you will see the name at the top.  When you move from control to control that name will change.  Something in there is called Admin, Referral, lblReferral, MonthSummaryCredits, etc.

Alternatively, just below the toolbar, there should be a dropdown box containing the names of all the controls on the form.  Can you post that list of names?
0
GRayLCommented:
If there are lots of controls, open the form in design view, go to the immediate pane of the VB Editor (Ctl-G or Alt-F11) and type:

for each con in forms!monthsummary:? con.name:next con

you will then get a list of all the controls on the form which you can then paste in a reply here.
0
GRayLCommented:
Sorry:

for each con in forms!monthsummary.controls: ? con.name: next con
0
smagaricAuthor Commented:
ok here's the result of all the controls on the form;

for each con in forms!monthsummary:? con.name:next con
MonthView2
cmbMonth
Label13
MonthSummaryCredits
CurrentAccountID
MonthSummaryDebits
cmbFund
Label15
Admin
Label21
Referral
Label23
ShowMonth
cmdMember
lblReferral
MonthlyBalance
IDModule
0
harfangCommented:
> Any ideas why?

Ray has already established that AccountID and Period are both numeric fields. However, they can hold a Null value... That would result in the criteria "AccountID =  and Period = ", which would produce and error (although I'm getting "missing operator"...).

Try this, perhaps:

Option Explicit   ' <--- make sure you have this at the top of the module

Private Sub Form_Current()

Dim strCriteria As String

If IsNull(AccountID) Or IsNull(Period) Then
    strCriteria = "False"
Else
    strCriteria = ""AccountID = " & AccountID & " and Period = " & Period
End If

With Forms!MonthSummary.Form
    .Admin = DLookup("AdminPerc", "MemberDetail", strCriteria)
    .Referral = DLookup("ReferralPerc", "MemberDetail", strCriteria)

    strCriteria = IIf( IsNull(AccountID), "False", "AccountID = " & AccountID )
    ReferralOnly = DLookup("ReferralOnly", "MemberAccounts", strCriteria)

    .lblReferral.Visible = ReferralOnly
    .MonthSummaryCredits.Enabled = Not ReferralOnly
    .MonthSummaryDebits.Enabled = Not ReferralOnly

End With
   
End Sub

The above would take care of possible Null values. There are of course several different way to accomplish the same thing.

Good luck!
(°v°)
0
smagaricAuthor Commented:
I'm now getting a syntax error, variable not defined;


   ReferralOnly = DLookup("ReferralOnly", "MemberAccounts", strCriteria)

Steve
0
harfangCommented:
Good! That's why I wanted the Option Explicit!

I'm guessing it's "ReferralOnly". Is that a variable, a field, a control? If it's a variable, say of boolean type, you could use this:

    Dim ReferralOnly As Boolean   ' near the top

    ReferralOnly = Nz( DLookup("ReferralOnly", "MemberAccounts", strCriteria), False )

Cheers!
(°v°)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smagaricAuthor Commented:
Thanks to all for your help.

Steve
0
harfangCommented:
Glad to help, good luck with your project!
(°v°)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.