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

syntax error

P: 56
How can I tie the users shortname to this code. so it comes back with the results that only apply to them.

Dim pastdue As Integer
Dim current As Integer
Dim tot As Integer

nodate = DCount("*", "TARA", "isNull(penddte)")
pastdue = DCount("*", "TARA", "penddte < date()")
current = DCount("*", "TARA", "penddte >= date()")
tot = DCount("*", "TARA")

Debug.Print nodate, pastdue, current, tot
Me.message = "You have " & tot & " items with " & pastdue & " past due, " & current & " current and " & nodate & " with no pending date"


End Sub


Right now it totals the entire database. I have a text box called txtusername that captures the users shortname when they login (using a function) that same shortname is in the maintable and that field is called Login id.

So what I would want to say is: count the number of items where the "penddte"(another field in the maintable) isnull and the txtusername is = to the field called loginid. I don't think I have my syntax correct. i get an cancell operation error.
nodate = DCount("*", "TARA", "isNull(penddte)" & " and [loginID]= " & Me.txtUserName)
Mar 21 '07 #1
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...
Expand|Select|Wrap|Line Numbers
  1. nodate = DCount("*", "TARA", "[penddte] Is Null" & " and [loginID]= '" & Me.txtUserName & "'")
Mary
Mar 21 '07 #2

P: 56
Try this ...
Expand|Select|Wrap|Line Numbers
  1. nodate = DCount("*", "TARA", "[penddte] Is Null" & " and [loginID]= '" & Me.txtUserName & "'")
Mary

thank you, It work on all lines but the last one:

Expand|Select|Wrap|Line Numbers
  1. tot = DCount("*", "TARA" & " and [loginID]= '" & Me.txtUserName & "'")
Mar 21 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1.  tot = DCount("*", "TARA" & "[loginID]= '" & Me.txtUserName & "'")
Mar 21 '07 #4

P: 56
Try this ...

Expand|Select|Wrap|Line Numbers
  1.  tot = DCount("*", "TARA" & "[loginID]= '" & Me.txtUserName & "'")

I get an error that says the microsoft engine can not find the input table or query "tara[loginid]="myshortname" make sure it exit and it is spelled correcty.

which both are.
Mar 21 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
I get an error that says the microsoft engine can not find the input table or query "tara[loginid]="myshortname" make sure it exit and it is spelled correcty.

which both are.
Sorry my fault for not checking the syntax.

Expand|Select|Wrap|Line Numbers
  1. tot = DCount("*", "TARA", "[loginID]= '" & Me.txtUserName & "'")
Mar 21 '07 #6

P: 56
Sorry my fault for not checking the syntax.

Expand|Select|Wrap|Line Numbers
  1. tot = DCount("*", "TARA", "[loginID]= '" & Me.txtUserName & "'")

Thank you so much. This works!!
Mar 21 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
You're welcome.
Mar 21 '07 #8

Post your reply

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