count number of visit on form

  • Thread starter Thread starter Moon
  • Start date Start date
M

Moon

Hi all,
I realize this has been posted before but haven't found the solution so
far.
I have a client table and visit table with a one to many relationship.
On my form for visits, I want a text box that would generate the visit
number for the client. so on their second visit, the text box should
generate '2'.
Any help is greatly appreciated.
moon
 
Hi all,
I realize this has been posted before but haven't found the solution so
far.
I have a client table and visit table with a one to many relationship.
On my form for visits, I want a text box that would generate the visit
number for the client. so on their second visit, the text box should
generate '2'.
Any help is greatly appreciated.
moon
Click to expand...

It depends on how your tables are structured. Assuming that you have a
visit-date field in the visits table, you could set the Control Source
of a textbox on the subform to

=DCount("*", "[Visits]", "[VisitDate] <= #" & [VisitDate] & "# AND
ClientID = " & [ClientID])

adjusting for your table and fieldnames of course. This will count all
visits up to and including the current visit for this client.

John W. Vinson[MVP]
 
Hi,
I do have a visit date in my Visits table and renamed it with my table
and field names to this:

=DCount("*","[Visits]","[visit_date] <= #" & [visit_date] & "# AND
client_id = " & [client_id])

However I'm getting #Error in my textbox. I have the textbox on a
subform of the Client Form.
John said:
Hi all,
I realize this has been posted before but haven't found the solution so
far.
I have a client table and visit table with a one to many relationship.
On my form for visits, I want a text box that would generate the visit
number for the client. so on their second visit, the text box should
generate '2'.
Any help is greatly appreciated.
moon
Click to expand...

It depends on how your tables are structured. Assuming that you have a
visit-date field in the visits table, you could set the Control Source
of a textbox on the subform to

=DCount("*", "[Visits]", "[VisitDate] <= #" & [VisitDate] & "# AND
ClientID = " & [ClientID])

adjusting for your table and fieldnames of course. This will count all
visits up to and including the current visit for this client.

John W. Vinson[MVP]
Click to expand...
 
Hi,
I do have a visit date in my Visits table and renamed it with my table
and field names to this:

=DCount("*","[Visits]","[visit_date] <= #" & [visit_date] & "# AND
client_id = " & [client_id])

However I'm getting #Error in my textbox. I have the textbox on a
subform of the Client Form.
Click to expand...

Is the field named [Visit Date] - with a blank - or [Visit_Date] -
with an underscore? They must match. Similarly for the Client_ID. I
prefer to avoid both blanks and underscores in fieldnames, using
"camel case" such as VisitDate and ClientID; that's a personal style
issue though and provided you're consistant it shouldn't matter much.

If you have a textbox or control on the subform bound to the
Visit(_)Date field, try using the Name property of that textbox rather
than the name of the field to which it is bound:

=DCount("*","[Visits]","[visit_date] <= #" & [txtVisit_date] & "# AND
client_id = " & [txtClient_id])

Note that this will indeed return an Error if either textbox is empty
(as it will be on the New Record). You can suppress this by checking
for NULL:

=IIF(IsNull(Me!txtVisitDate) Or IsNull(Me!txtClientID), Null,
DCount("*", "[Visits]","[visit_date] <= #" & [txtVisit_date] & "# AND
client_id = " & [txtClient_id]))


John W. Vinson[MVP]
 
Hi,
Sorry to be a pest but after I tried with your last function I'm
gettting the #Name? error. Here's is what I have. (I do have empty
visit dates)

=IIf(IsNull([Me]![txtvisit_date]) Or
IsNull([Me]![txtclient_id]),Null,DCount("*","[Visits]","[visit_date] <=
#" & [txtvisit_date] & "# AND client_id = " & [txtclient_id]))
 
Hi,
Sorry to be a pest but after I tried with your last function I'm
gettting the #Name? error. Here's is what I have. (I do have empty
visit dates)

=IIf(IsNull([Me]![txtvisit_date]) Or
IsNull([Me]![txtclient_id]),Null,DCount("*","[Visits]","[visit_date] <=
#" & [txtvisit_date] & "# AND client_id = " & [txtclient_id]))
Click to expand...

Sorry... my mistake! The Me! is appropriate for VBA code but not for
the Control Source of a form control. Just use the name of the control
itself:

=IIf(IsNull([txtvisit_date]) Or IsNull([txtclient_id]), Null,
DCount("*","[Visits]","[visit_date] <= #" & [txtvisit_date] & "# AND
client_id = " & [txtclient_id]))

Again, this assumes that the table is named Visits, that it contains
fields named visit_date and client_id, and that the Form has controls
bound to these fields named txtvisit_date and txtclient_id
respectively. Doublecheck the names of the Form controls!

John W. Vinson[MVP]
 
Hi again,
Thanks for bearing with me so far; I have the function exactly as
you've written and have triple checked all my form names but am still
getting #Error..Is this possible???
John said:
Hi,
Sorry to be a pest but after I tried with your last function I'm
gettting the #Name? error. Here's is what I have. (I do have empty
visit dates)

=IIf(IsNull([Me]![txtvisit_date]) Or
IsNull([Me]![txtclient_id]),Null,DCount("*","[Visits]","[visit_date] <=
#" & [txtvisit_date] & "# AND client_id = " & [txtclient_id]))
Click to expand...

Sorry... my mistake! The Me! is appropriate for VBA code but not for
the Control Source of a form control. Just use the name of the control
itself:

=IIf(IsNull([txtvisit_date]) Or IsNull([txtclient_id]), Null,
DCount("*","[Visits]","[visit_date] <= #" & [txtvisit_date] & "# AND
client_id = " & [txtclient_id]))

Again, this assumes that the table is named Visits, that it contains
fields named visit_date and client_id, and that the Form has controls
bound to these fields named txtvisit_date and txtclient_id
respectively. Doublecheck the names of the Form controls!

John W. Vinson[MVP]
Click to expand...
 
Hi again,
Thanks for bearing with me so far; I have the function exactly as
you've written and have triple checked all my form names but am still
getting #Error..Is this possible???
Click to expand...

Please post the exact string you're using and - probably more
important - exactly where you are using it. I was assuming you'ld use
it as the Control Source property of a form textbox. If so, please
post the name of the Form, and the Name property of this control and
of the other controls that the expression references.

John W. Vinson[MVP]
 
Hi,
Here is the string I put in the control source of my form text box:

=IIf(IsNull([txtvisit_date]) Or
IsNull([txtclient_id]),Null,DCount("*","[Visits]","[visit_date] <= #" &
[txtvisit_date] & "# AND
client_id = " & [txtclient_id]))

My form is a subform named ' Visits subform'. The other Name property
of the rest of the controls are:

visit_date : txtvisit_date
client_id : txtclient_id

The field names of visit_date and client_id in my table Visits are
visit_date and client_id respectively.
 
Hi,
Here is the string I put in the control source of my form text box:

=IIf(IsNull([txtvisit_date]) Or
IsNull([txtclient_id]),Null,DCount("*","[Visits]","[visit_date] <= #" &
[txtvisit_date] & "# AND
client_id = " & [txtclient_id]))

My form is a subform named ' Visits subform'. The other Name property
of the rest of the controls are:

visit_date : txtvisit_date
client_id : txtclient_id

The field names of visit_date and client_id in my table Visits are
visit_date and client_id respectively.
Click to expand...

VERY odd. I can't see *anything* wrong with this.

All I can suggest is that you try breaking it down - start with

=IIf(IsNull([txtvisit_date]) Or
IsNull([txtclient_id]),Null,"OK")

to see if it shows OK if both other controls are filled. Then use

=DCount("*","[Visits]")

to see if it can count all visits; then

DCount("*","[Visits]","[ClientID] = " & [txtClient_ID])

and then keep building up.

Oh... one possible question: is ClientID a Text datatype (rather than
a number)? if so, you need quotemarks:

=IIf(IsNull([txtvisit_date]) Or IsNull([txtclient_id]),
Null,DCount("*","[Visits]",
"[visit_date] <= #" & [txtvisit_date] & "# AND
client_id = '" & [txtclient_id] & "'"))

John W. Vinson[MVP]
 
Hi,
I tired breaking them down like you suggested and it all worked until
DCount("*","[Visits]","[client_id] = " & [txtclient_id]) which gave me
a # Error.

My client_id is a text field; sorry I should have mentioned it. However
when I tried the below with the quotes, it still gave me # Errror.

=IIf(IsNull([txtvisit_date]) Or IsNull([txtclient_id]),
Null,DCount("*","[Visits]",
"[visit_date] <= #" & [txtvisit_date] & "# AND
client_id = '" & [txtclient_id] & "'"))

John said:
Hi,
Here is the string I put in the control source of my form text box:

=IIf(IsNull([txtvisit_date]) Or
IsNull([txtclient_id]),Null,DCount("*","[Visits]","[visit_date] <= #" &
[txtvisit_date] & "# AND
client_id = " & [txtclient_id]))

My form is a subform named ' Visits subform'. The other Name property
of the rest of the controls are:

visit_date : txtvisit_date
client_id : txtclient_id

The field names of visit_date and client_id in my table Visits are
visit_date and client_id respectively.
Click to expand...

VERY odd. I can't see *anything* wrong with this.

All I can suggest is that you try breaking it down - start with

=IIf(IsNull([txtvisit_date]) Or
IsNull([txtclient_id]),Null,"OK")

to see if it shows OK if both other controls are filled. Then use

=DCount("*","[Visits]")

to see if it can count all visits; then

DCount("*","[Visits]","[ClientID] = " & [txtClient_ID])

and then keep building up.

Oh... one possible question: is ClientID a Text datatype (rather than
a number)? if so, you need quotemarks:

=IIf(IsNull([txtvisit_date]) Or IsNull([txtclient_id]),
Null,DCount("*","[Visits]",
"[visit_date] <= #" & [txtvisit_date] & "# AND
client_id = '" & [txtclient_id] & "'"))

John W. Vinson[MVP]
Click to expand...
 
My client_id is a text field; sorry I should have mentioned it. However
when I tried the below with the quotes, it still gave me # Errror.

=IIf(IsNull([txtvisit_date]) Or IsNull([txtclient_id]),
Null,DCount("*","[Visits]",
"[visit_date] <= #" & [txtvisit_date] & "# AND
client_id = '" & [txtclient_id] & "'"))
Click to expand...

Groping in the dark here but... how are you entering the date into
txtvisit_date? Try changing this to use

"[visit_date] <= #" & Format([txtvisit_date],"mm/dd/yyyy") & "# AND

to explicitly cast the visit date into a form Access will accept.

John W. Vinson[MVP]
 
Hi,
Thank you so much for trying patiently to help and it has finally
worked! You were right, my txtvisit_date has a format of dd/mm/yyyy. So
I tried with your Format string but it gave me a #name error. So I got
rid of the format in the txtvisit_date field and tried it with the
original string:
=IIf(IsNull([txtvisit_date]) Or IsNull([txtclient_id]),
Null,DCount("*","[Visits]",
"[visit_date] <= #" & [txtvisit_date] & "# AND
client_id = '" & [txtclient_id] & "'"))

And I reformatted the txtvisit_date back to dd/mm/yyyy and the string
above still worked!
Not sure why though.

Thank you again so much!!!
Moon
 
Back
Top