DMax Problem (Type Mismatch)

[DS]

I have this DMax on a Number field, It works fine.

SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1

I need to add a Letter to the begining of the generated Number.
The letter comes from an Unbound field on the same form
The field is Called SType....Which can be anyone of 5 Letters...
I tried this...

SalesID = "[SType]"& Nz(DMax("[SalesID]", "Sales"), 0) + 1

and it came back as a Type MisMatch
What do I need to change or do to get this thing to work....I've scoured
the Internet all day long looking for a solution, 8 hours later and I
still can't get it to work.
Any help is appreciated.
Thanks
DS

 

[fredg]

I have this DMax on a Number field, It works fine.

SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1

I need to add a Letter to the begining of the generated Number.
The letter comes from an Unbound field on the same form
The field is Called SType....Which can be anyone of 5 Letters...
I tried this...

SalesID = "[SType]"& Nz(DMax("[SalesID]", "Sales"), 0) + 1

and it came back as a Type MisMatch
What do I need to change or do to get this thing to work....I've scoured
the Internet all day long looking for a solution, 8 hours later and I
still can't get it to work.
Any help is appreciated.
Thanks
DS

Remove the quotes around [SType] so that it picks up the value IN
[SType] rather than the literal characters [SType].

SalesID = [SType] & Nz(DMax("[SalesID]", "Sales"), 0) + 1

 

[DS]

I have this DMax on a Number field, It works fine.

SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1

I need to add a Letter to the begining of the generated Number.
The letter comes from an Unbound field on the same form
The field is Called SType....Which can be anyone of 5 Letters...
I tried this...

SalesID = "[SType]"& Nz(DMax("[SalesID]", "Sales"), 0) + 1

and it came back as a Type MisMatch
What do I need to change or do to get this thing to work....I've scoured
the Internet all day long looking for a solution, 8 hours later and I
still can't get it to work.
Any help is appreciated.
Thanks
DS

Remove the quotes around [SType] so that it picks up the value IN
[SType] rather than the literal characters [SType].

SalesID = [SType] & Nz(DMax("[SalesID]", "Sales"), 0) + 1

Thanks, I tried it and it say, "The Value that you've entered isn't
valid for this field." SType is either E D T P Q and its a Text Field.
DS

 

[Guest]

Hi there
I presume that you've made the SalesID field a text field (and not a number
field) within the table.
Also, how about putting the whole of the NZ function in brackets - ie.

SalesID = [SType] & (Nz(DMax("[SalesID]", "Sales"), 0) + 1)

Hope this helps.
Lee

I have this DMax on a Number field, It works fine.

SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1

I need to add a Letter to the begining of the generated Number.
The letter comes from an Unbound field on the same form
The field is Called SType....Which can be anyone of 5 Letters...
I tried this...

SalesID = "[SType]"& Nz(DMax("[SalesID]", "Sales"), 0) + 1

and it came back as a Type MisMatch
What do I need to change or do to get this thing to work....I've scoured
the Internet all day long looking for a solution, 8 hours later and I
still can't get it to work.
Any help is appreciated.
Thanks
DS

Remove the quotes around [SType] so that it picks up the value IN
[SType] rather than the literal characters [SType].

SalesID = [SType] & Nz(DMax("[SalesID]", "Sales"), 0) + 1

Thanks, I tried it and it say, "The Value that you've entered isn't
valid for this field." SType is either E D T P Q and its a Text Field.
DS

 

[DS]

Hi there
I presume that you've made the SalesID field a text field (and not a number
field) within the table.
Also, how about putting the whole of the NZ function in brackets - ie.

SalesID = [SType] & (Nz(DMax("[SalesID]", "Sales"), 0) + 1)

Hope this helps.
Lee

:

On Wed, 08 Jun 2005 16:17:39 -0400, DS wrote:



I have this DMax on a Number field, It works fine.

SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1

I need to add a Letter to the begining of the generated Number.
The letter comes from an Unbound field on the same form
The field is Called SType....Which can be anyone of 5 Letters...
I tried this...

SalesID = "[SType]"& Nz(DMax("[SalesID]", "Sales"), 0) + 1

and it came back as a Type MisMatch
What do I need to change or do to get this thing to work....I've scoured
the Internet all day long looking for a solution, 8 hours later and I
still can't get it to work.
Any help is appreciated.
Thanks
DS


Remove the quotes around [SType] so that it picks up the value IN
[SType] rather than the literal characters [SType].

SalesID = [SType] & Nz(DMax("[SalesID]", "Sales"), 0) + 1

Thanks, I tried it and it say, "The Value that you've entered isn't
valid for this field." SType is either E D T P Q and its a Text Field.
DS

SalesID is a Number field.
Thanks
DS

 

[Douglas J. Steele]

SalesID is a Number field.

I've posted an answer in microsoft.public.access. If you feel you need to
post to more than one group (HINT: it's seldom necessary), please have the
courtesy to cross-post (send the one message to all groups at once), rather
than multi-post (send individual messages to each group). In this way, all
responses to your post will be available together, regardless of what group
the responder was in, and the rest of us won't have to read your post
multiple times. (It also uses fewer server resources)

If SalesID is a numeric field, then you can't expect to store text in it.

 

[DS]

I've posted an answer in microsoft.public.access. If you feel you need to
post to more than one group (HINT: it's seldom necessary), please have the
courtesy to cross-post (send the one message to all groups at once), rather
than multi-post (send individual messages to each group). In this way, all
responses to your post will be available together, regardless of what group
the responder was in, and the rest of us won't have to read your post
multiple times. (It also uses fewer server resources)

If SalesID is a numeric field, then you can't expect to store text in it.

Hi Doug, how does one Multi-Post? I apologize.
DS

 

[Douglas J. Steele]

Hi Doug, how does one Multi-Post? I apologize.

Sorry: it's been too long since I've used the Mozilla reader, so I don't
remember the details.

In Outlook Express, you click the "Newsgroups:" label to the left of the box
containing the name of the current newsgroup. That will open a dialog that
will let you add additional newsgroups to your post.

In Forte Agent, you type each of the newsgroup names in the Newsgroups line
at the top of the message editing screen

 

[DS]

Sorry: it's been too long since I've used the Mozilla reader, so I don't
remember the details.

In Outlook Express, you click the "Newsgroups:" label to the left of the box
containing the name of the current newsgroup. That will open a dialog that
will let you add additional newsgroups to your post.

In Forte Agent, you type each of the newsgroup names in the Newsgroups line
at the top of the message editing screen

I'm really sorry...I didn't mean to cause any confusion. Once again my
apologies.

Doug this one worked after I changed the box from number to text.
Is this going to kill me down the road to have it be a Text instead of
number?

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales"), "A0"), 2)) + 1

Also since I chaged SalesID to text this expression doesn't work...any
idea why.

=Nz(DSum("[Expr1]","SalesDetailsQ","[SalesID]=" &
[Forms]![Sales]![SalesID] & " AND [SDTaxed]=True"),0)

Thanks
DS

 

[fredg]

Hi there
I presume that you've made the SalesID field a text field (and not a number
field) within the table.
Also, how about putting the whole of the NZ function in brackets - ie.

SalesID = [SType] & (Nz(DMax("[SalesID]", "Sales"), 0) + 1)

Hope this helps.
Lee

fredg wrote:

On Wed, 08 Jun 2005 16:17:39 -0400, DS wrote:



I have this DMax on a Number field, It works fine.

SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1

I need to add a Letter to the begining of the generated Number.
The letter comes from an Unbound field on the same form
The field is Called SType....Which can be anyone of 5 Letters...
I tried this...

SalesID = "[SType]"& Nz(DMax("[SalesID]", "Sales"), 0) + 1

and it came back as a Type MisMatch
What do I need to change or do to get this thing to work....I've scoured
the Internet all day long looking for a solution, 8 hours later and I
still can't get it to work.
Any help is appreciated.
Thanks
DS


Remove the quotes around [SType] so that it picks up the value IN
[SType] rather than the literal characters [SType].

SalesID = [SType] & Nz(DMax("[SalesID]", "Sales"), 0) + 1

Thanks, I tried it and it say, "The Value that you've entered isn't
valid for this field." SType is either E D T P Q and its a Text Field.
DS

SalesID is a Number field.
Thanks
DS

Well, if SalesID is a Number field and [SType] is a Text field, how
can SalesID have a letter value of "E" plus a number?
A number field cannot contain letters!
Either change what you are trying to do, or change SalesID to Text.

 

[DS]

Baby Face Lee wrote:

Hi there
I presume that you've made the SalesID field a text field (and not a number
field) within the table.
Also, how about putting the whole of the NZ function in brackets - ie.

SalesID = [SType] & (Nz(DMax("[SalesID]", "Sales"), 0) + 1)

Hope this helps.
Lee

:


fredg wrote:


On Wed, 08 Jun 2005 16:17:39 -0400, DS wrote:




I have this DMax on a Number field, It works fine.

SalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1

I need to add a Letter to the begining of the generated Number.
The letter comes from an Unbound field on the same form
The field is Called SType....Which can be anyone of 5 Letters...
I tried this...

SalesID = "[SType]"& Nz(DMax("[SalesID]", "Sales"), 0) + 1

and it came back as a Type MisMatch
What do I need to change or do to get this thing to work....I've scoured
the Internet all day long looking for a solution, 8 hours later and I
still can't get it to work.
Any help is appreciated.
Thanks
DS


Remove the quotes around [SType] so that it picks up the value IN
[SType] rather than the literal characters [SType].

SalesID = [SType] & Nz(DMax("[SalesID]", "Sales"), 0) + 1

Thanks, I tried it and it say, "The Value that you've entered isn't
valid for this field." SType is either E D T P Q and its a Text Field.
DS

SalesID is a Number field.
Thanks
DS

Well, if SalesID is a Number field and [SType] is a Text field, how
can SalesID have a letter value of "E" plus a number?
A number field cannot contain letters!
Either change what you are trying to do, or change SalesID to Text.

Fred,

I made SalesID a number field and that works but why is my field that
sums things up now not working since I changed the SalesID to Text? Is
the Syntax wrong? It comes up as #Error

Thanks
DS

=Nz(DSum("[Expr1]","SalesDetailsQ","[SalesID]=" &
[Forms]![Sales]![SalesID] & " AND [SDTaxed]=True"),0)

 

[Douglas J. Steele]

Sorry: it's been too long since I've used the Mozilla reader, so I don't
remember the details.

In Outlook Express, you click the "Newsgroups:" label to the left of the
box containing the name of the current newsgroup. That will open a dialog
that will let you add additional newsgroups to your post.

In Forte Agent, you type each of the newsgroup names in the Newsgroups
line at the top of the message editing screen

I'm really sorry...I didn't mean to cause any confusion. Once again my
apologies.

Doug this one worked after I changed the box from number to text.
Is this going to kill me down the road to have it be a Text instead of
number?

Me!SalesID = SType & CLng(Mid(Nz(DMax("[SalesID]", "Sales"), "A0"), 2)) +
1

Also since I chaged SalesID to text this expression doesn't work...any
idea why.

=Nz(DSum("[Expr1]","SalesDetailsQ","[SalesID]=" &
[Forms]![Sales]![SalesID] & " AND [SDTaxed]=True"),0)

Check the thread in microsoft.public.access: I've given you explicit details
for both how to deal with it as Text (yesterday), plus how you should really
do it (today)