• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

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
0
smagaric
Asked:
smagaric
  • 7
  • 7
  • 3
  • +2
1 Solution
 
Ryan ChongCommented:
>>incorrect syntax near keyword 'and'

try check and make sure AccountID returns a numeric value ?
0
 
Ryan ChongCommented:
as well as Period...
0
 
smagaricAuthor Commented:
AccountID and Period are of type int in the SQL db.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

Forms!MonthSummary.Admin
or
Me.admin


Pete
0
 
Ryan ChongCommented:
>>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
 
smagaricAuthor Commented:
Thanks to all for your help.

Steve
0
 
harfangCommented:
Glad to help, good luck with your project!
(°v°)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now