Help with DMAX

S

Spidey3721

Making a service call tracking database. Have one form ([service call form])
that shows outstanding service calls - this form has a button that is to
bring up a form where I can enter in a new incident associated with that
service call

Example - Service call # = 301.003 (ServiceCallID) (text)
Associated service call incidents = 301.003.01; 301.003.02;
etc...(IncidentID) (text)

I am trying to get a procedure in the second form to calculates the max
IncidentID (which is text) where the first 3 number characters match the
first 3 number characters of the ServiceCallID textbox ([ServiceCallID
text]) from the first Form...

CODE:

StrMax = Nz(DMax("[IncidentID]", "[Service call incident
table]",Left([IncidentID],3) = Left (Forms![service call
form].[ServiceCallID text],3) , 0))


I am trying to get a textbox value in an input form to defualt to the next
incremetal value that is available for
 
D

Dirk Goldgar

Spidey3721 said:
Making a service call tracking database. Have one form ([service call
form]) that shows outstanding service calls - this form has a button
that is to bring up a form where I can enter in a new incident
associated with that service call

Example - Service call # = 301.003 (ServiceCallID) (text)
Associated service call incidents = 301.003.01; 301.003.02;
etc...(IncidentID) (text)

I am trying to get a procedure in the second form to calculates the
max IncidentID (which is text) where the first 3 number characters
match the first 3 number characters of the ServiceCallID textbox
([ServiceCallID text]) from the first Form...

CODE:

StrMax = Nz(DMax("[IncidentID]", "[Service call incident
table]",Left([IncidentID],3) = Left (Forms![service call
form].[ServiceCallID text],3) , 0))


I am trying to get a textbox value in an input form to defualt to the
next incremetal value that is available for

This would be easier if: (1) you were not compounding your keys into a
single field using your "dot" notation, but rather had three separate
fields stored in the table as a compound key, instead of as one field
with two or three pieces; and (2) you didn't use spaces in your table,
form, and control names. The way you have it set up now, you will
continually have problems working with these tables and forms as you
move forward in your design.

However, it should still be possible to make your DMax expression work.
Try this:

StrMax = _
Nz( _
DMax( _
"[IncidentID]", _
"[Service call incident table]", _
"Left([IncidentID],3)=Left(" & _
"Forms![service call form].[ServiceCallID text],3)" _
) , _
0)

I've tried to format that with line-continuation characters so that the
lines won't wrap in the newreader.
 
S

Spidey3721

Thank you -

Understand the advice on the spaces, but I'm pretty lost on your first tip

Could you please elaborate on this one - I'm sorry, but I'm not sure what
you mean when you say that I'm compounding my keys, etc...
This would be easier if: (1) you were not compounding your keys into a
single field using your "dot" notation, but rather had three separate
fields stored in the table as a compound key, instead of as one field
with two or three pieces;



Dirk Goldgar said:
Spidey3721 said:
Making a service call tracking database. Have one form ([service call
form]) that shows outstanding service calls - this form has a button
that is to bring up a form where I can enter in a new incident
associated with that service call

Example - Service call # = 301.003 (ServiceCallID) (text)
Associated service call incidents = 301.003.01; 301.003.02;
etc...(IncidentID) (text)

I am trying to get a procedure in the second form to calculates the
max IncidentID (which is text) where the first 3 number characters
match the first 3 number characters of the ServiceCallID textbox
([ServiceCallID text]) from the first Form...

CODE:

StrMax = Nz(DMax("[IncidentID]", "[Service call incident
table]",Left([IncidentID],3) = Left (Forms![service call
form].[ServiceCallID text],3) , 0))


I am trying to get a textbox value in an input form to defualt to the
next incremetal value that is available for

This would be easier if: (1) you were not compounding your keys into a
single field using your "dot" notation, but rather had three separate
fields stored in the table as a compound key, instead of as one field
with two or three pieces; and (2) you didn't use spaces in your table,
form, and control names. The way you have it set up now, you will
continually have problems working with these tables and forms as you
move forward in your design.

However, it should still be possible to make your DMax expression work.
Try this:

StrMax = _
Nz( _
DMax( _
"[IncidentID]", _
"[Service call incident table]", _
"Left([IncidentID],3)=Left(" & _
"Forms![service call form].[ServiceCallID text],3)" _
) , _
0)

I've tried to format that with line-continuation characters so that the
lines won't wrap in the newreader.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
S

Spidey3721

Thank you -

Understand the advice on the spaces, but I'm pretty lost on your first tip

Could you please elaborate on this one - I'm sorry, but I'm not sure what
you mean when you say that I'm compounding my keys, etc...
This would be easier if: (1) you were not compounding your keys into a
single field using your "dot" notation, but rather had three separate
fields stored in the table as a compound key, instead of as one field
with two or three pieces;


Dirk Goldgar said:
Spidey3721 said:
Making a service call tracking database. Have one form ([service call
form]) that shows outstanding service calls - this form has a button
that is to bring up a form where I can enter in a new incident
associated with that service call

Example - Service call # = 301.003 (ServiceCallID) (text)
Associated service call incidents = 301.003.01; 301.003.02;
etc...(IncidentID) (text)

I am trying to get a procedure in the second form to calculates the
max IncidentID (which is text) where the first 3 number characters
match the first 3 number characters of the ServiceCallID textbox
([ServiceCallID text]) from the first Form...

CODE:

StrMax = Nz(DMax("[IncidentID]", "[Service call incident
table]",Left([IncidentID],3) = Left (Forms![service call
form].[ServiceCallID text],3) , 0))


I am trying to get a textbox value in an input form to defualt to the
next incremetal value that is available for

This would be easier if: (1) you were not compounding your keys into a
single field using your "dot" notation, but rather had three separate
fields stored in the table as a compound key, instead of as one field
with two or three pieces; and (2) you didn't use spaces in your table,
form, and control names. The way you have it set up now, you will
continually have problems working with these tables and forms as you
move forward in your design.

However, it should still be possible to make your DMax expression work.
Try this:

StrMax = _
Nz( _
DMax( _
"[IncidentID]", _
"[Service call incident table]", _
"Left([IncidentID],3)=Left(" & _
"Forms![service call form].[ServiceCallID text],3)" _
) , _
0)

I've tried to format that with line-continuation characters so that the
lines won't wrap in the newreader.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
D

Dirk Goldgar

Spidey3721 said:
Thank you -

Understand the advice on the spaces, but I'm pretty lost on your
first tip

Could you please elaborate on this one - I'm sorry, but I'm not sure
what you mean when you say that I'm compounding my keys, etc...

You gave examples of keys like these:

ServiceCallID = 301.003
IncidentID = 301.003.01

That implies that we are dealing with three "atomic" pieces of
information -- "301", "003", and "01". Let's call these "subkeys". But
these subkeys are being stored all in one field. That makes it
difficult to select records based on only one or two of the subkeys;
you now need complex expressions involving VBA functions to break apart
each key field into its subfields. Besides being a complicated process,
it makes it impossible for the database engine to use indexes on the
subkeys, so queries based on them will inevitably be inefficient. Also,
if these subkeys are numeric, you are forced to format them as text and
ensure that each subkey is always the same length with leading zeros,
again requiring additional code and care on your part.

A better structure would be to have a separate field in your table for
each subkey, and create a compound key comprising all the subkey fields.
A compound key or index is one that includes multiple fields. You can
easily create a compound primary key in Access by selecting (in table
design view) multiple fields, and then clicking the "key" icon on the
toolbar. You can create a non-primary compound index by way of the
Indexes dialog.

I don't know what the subkeys in ServiceCallID represent in your system,
so I'll just call them SvcIDA (e.g., 301) and SvcIDB (e.g., 003). The
last subkey in IncidentID, as I understand it, is a sequence number, so
I'll call that IncidentSequence. If these fields are all always
numeric, I'd suggest using number fields -- Integer or Long Integer --
to store them, rather than text. In the scheme I'm proposing, your
ServiceCalls table would have a compound primary key comprising the two
fields SvcIDA and SvcIDB, while your Incidents table would have a
three-field compound key comprising SvcIDA, SvcIDB, and
IncidentSequence.

To display these fields in a single text box, you can always use a
calculated control that formats and concatenates the true key fields;
for example,

=Format([SvcIDA], "000.") &
Format([SvcIDB], "000.") &
Format([IncidentSequence], "00")

With a structure like this, you can come up with a new incident number
for a given service call using code like this:

Dim intNewSequence As Integer

intNewSequence = 1 + _
Nz( _
DMax( _
"IncidentSequence", _
"[Service call incident table]", _
"SvcIDA=" & Me!SvcIDA & " AND SvcIDB=" & Me!SvcIDB" _
) , _
0)

That may not look like such a big improvement by itself, over what you
had before, but not only will it will be more efficient, but you can
easily set up form/subform arrangements between ServiceCalls and
Incidents, using SvcIDA and SvcIDB as the linking fields, and you can
easily write queries that extract records based on SvcIDA and/or SvcIDB.
 
S

Spidey3721

This all seems to make sense - Thank you for the detailed lesson - Very big
help...

I am revising my structure based on your advice.

Question: the part about using a calculated control to concatenate the
different fields together:

I made all three different fields - all are integer number fields where
"009" registers in as "9" for now . Not sure if this is okay...

If I want to show this number in a textbox, are you saying I can type:
=format([svcIDA], "000.") in the control source and it will show SvcIDA as
"009" ???. Concatenation issue aside for now - The main question is - do I
enter this into the control source property ? I am entering it in as above
and I'm getting an #Error...

Is there something wrong with my number formatting - I'm missing
something....



Dirk Goldgar said:
Spidey3721 said:
Thank you -

Understand the advice on the spaces, but I'm pretty lost on your
first tip

Could you please elaborate on this one - I'm sorry, but I'm not sure
what you mean when you say that I'm compounding my keys, etc...

You gave examples of keys like these:

ServiceCallID = 301.003
IncidentID = 301.003.01

That implies that we are dealing with three "atomic" pieces of
information -- "301", "003", and "01". Let's call these "subkeys". But
these subkeys are being stored all in one field. That makes it
difficult to select records based on only one or two of the subkeys;
you now need complex expressions involving VBA functions to break apart
each key field into its subfields. Besides being a complicated process,
it makes it impossible for the database engine to use indexes on the
subkeys, so queries based on them will inevitably be inefficient. Also,
if these subkeys are numeric, you are forced to format them as text and
ensure that each subkey is always the same length with leading zeros,
again requiring additional code and care on your part.

A better structure would be to have a separate field in your table for
each subkey, and create a compound key comprising all the subkey fields.
A compound key or index is one that includes multiple fields. You can
easily create a compound primary key in Access by selecting (in table
design view) multiple fields, and then clicking the "key" icon on the
toolbar. You can create a non-primary compound index by way of the
Indexes dialog.

I don't know what the subkeys in ServiceCallID represent in your system,
so I'll just call them SvcIDA (e.g., 301) and SvcIDB (e.g., 003). The
last subkey in IncidentID, as I understand it, is a sequence number, so
I'll call that IncidentSequence. If these fields are all always
numeric, I'd suggest using number fields -- Integer or Long Integer --
to store them, rather than text. In the scheme I'm proposing, your
ServiceCalls table would have a compound primary key comprising the two
fields SvcIDA and SvcIDB, while your Incidents table would have a
three-field compound key comprising SvcIDA, SvcIDB, and
IncidentSequence.

To display these fields in a single text box, you can always use a
calculated control that formats and concatenates the true key fields;
for example,

=Format([SvcIDA], "000.") &
Format([SvcIDB], "000.") &
Format([IncidentSequence], "00")

With a structure like this, you can come up with a new incident number
for a given service call using code like this:

Dim intNewSequence As Integer

intNewSequence = 1 + _
Nz( _
DMax( _
"IncidentSequence", _
"[Service call incident table]", _
"SvcIDA=" & Me!SvcIDA & " AND SvcIDB=" & Me!SvcIDB" _
) , _
0)

That may not look like such a big improvement by itself, over what you
had before, but not only will it will be more efficient, but you can
easily set up form/subform arrangements between ServiceCalls and
Incidents, using SvcIDA and SvcIDB as the linking fields, and you can
easily write queries that extract records based on SvcIDA and/or SvcIDB.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
D

Dirk Goldgar

(answers inline)

Spidey3721 said:
This all seems to make sense - Thank you for the detailed lesson -
Very big help...

I am revising my structure based on your advice.

Question: the part about using a calculated control to concatenate
the different fields together:

I made all three different fields - all are integer number fields
where "009" registers in as "9" for now . Not sure if this is okay...

Yes, that's fine. Leading zeros are never actually stored in number
fields, though you may set a format to display the stored number with
leading zeros if you like.
If I want to show this number in a textbox, are you saying I can type:
=format([svcIDA], "000.") in the control source and it will show
SvcIDA as "009" ???.

If you want to just show one of these fields in a text box, not
concatenating them together, you can use a bound text box with
properties set like this:

ControlSource: svcIDA
Format: 000

Now the field will be displayed with three digits and leading zeros.
Concatenation issue aside for now - The main
question is - do I enter this into the control source property ? I am
entering it in as above and I'm getting an #Error...

You're probably getting the error because the control is still named
"svcIDA". If you have a control that is named the same as a field in
the form's recordsource, the control must be bound directly to the
field, not to an expression. All you'd have to do to fix this is rename
the text box to "txtSvcIDA", or something like that. But in this case
you can leave the textbox bound directly to the field and set its Format
property instead, as I described above.
 
S

Spidey3721

Such a great help -

It was because I had the textbox named the same as the control source - I
think that this issue has been getting me for a LONG time, on many different
items...

THANK YOU...


Dirk Goldgar said:
(answers inline)

Spidey3721 said:
This all seems to make sense - Thank you for the detailed lesson -
Very big help...

I am revising my structure based on your advice.

Question: the part about using a calculated control to concatenate
the different fields together:

I made all three different fields - all are integer number fields
where "009" registers in as "9" for now . Not sure if this is okay...

Yes, that's fine. Leading zeros are never actually stored in number
fields, though you may set a format to display the stored number with
leading zeros if you like.
If I want to show this number in a textbox, are you saying I can type:
=format([svcIDA], "000.") in the control source and it will show
SvcIDA as "009" ???.

If you want to just show one of these fields in a text box, not
concatenating them together, you can use a bound text box with
properties set like this:

ControlSource: svcIDA
Format: 000

Now the field will be displayed with three digits and leading zeros.
Concatenation issue aside for now - The main
question is - do I enter this into the control source property ? I am
entering it in as above and I'm getting an #Error...

You're probably getting the error because the control is still named
"svcIDA". If you have a control that is named the same as a field in
the form's recordsource, the control must be bound directly to the
field, not to an expression. All you'd have to do to fix this is rename
the text box to "txtSvcIDA", or something like that. But in this case
you can leave the textbox bound directly to the field and set its Format
property instead, as I described above.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top