Conversion type failure CDec

[Bruce]

I am trying to create a unique number with the following.

Round((Rnd()*100000),0) & Format(Date(),"yymmdd") & [Region]![RegionID]

This create a number with 5 & 6 & 3 sig. figures (ie 14 digits) with non to
the right of the decimal.

I am trying to append this to a field of type decimal with precision 18 and
0 decimals.

It fails with conversion failure. I have tried ammending as below but it
tell me I have the wrong number of arguments...

CDec(Round((Rnd()*100000),0) & Format(Date(),"yymmdd") & [Region]![RegionID])

The help tells me it should be CDec(expression).

Any ideas....

Bruce

 

[JohnFol]

Hi Bruce, if I try and mimic what you have done in Access (using Access data
types) I can get the following to work

Dim x As Double
x = Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333

Dim y As Double
y = 1 + x

and so does
y = 1 + CDec(x)

and so does
x = CDec(Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333)

I think that since your problem relates to datatypes it might be worth
posting some of the variable declarations.

 

[Bruce]

Thanks John,

I may have left some key info out. I am running this as a caclulated field
in a query rather than from code. The query is an insert into action query.

When I use CDec as in the example below the full error message is;
'The expression you have entered has a function containing the wrong number
of arguments'

I have checked the properties of the destination field and they seem in order.

Regards,

Hi Bruce, if I try and mimic what you have done in Access (using Access data
types) I can get the following to work

Dim x As Double
x = Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333

Dim y As Double
y = 1 + x

and so does
y = 1 + CDec(x)

and so does
x = CDec(Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333)

I think that since your problem relates to datatypes it might be worth
posting some of the variable declarations.

I am trying to create a unique number with the following.

Round((Rnd()*100000),0) & Format(Date(),"yymmdd") & [Region]![RegionID]

This create a number with 5 & 6 & 3 sig. figures (ie 14 digits) with non
to
the right of the decimal.

I am trying to append this to a field of type decimal with precision 18
and
0 decimals.

It fails with conversion failure. I have tried ammending as below but it
tell me I have the wrong number of arguments...

CDec(Round((Rnd()*100000),0) & Format(Date(),"yymmdd") &
[Region]![RegionID])

The help tells me it should be CDec(expression).

Any ideas....

Bruce

 

[JohnFol]

If it helps anyone else identify the problem, I can simplify this greatly.
Just create an Append Query with the field being

=cdec(33)

Bruce I'll have a dig around

Thanks John,

I may have left some key info out. I am running this as a caclulated field
in a query rather than from code. The query is an insert into action
query.

When I use CDec as in the example below the full error message is;
'The expression you have entered has a function containing the wrong
number
of arguments'

I have checked the properties of the destination field and they seem in
order.

Regards,

Hi Bruce, if I try and mimic what you have done in Access (using Access
data
types) I can get the following to work

Dim x As Double
x = Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333

Dim y As Double
y = 1 + x

and so does
y = 1 + CDec(x)

and so does
x = CDec(Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333)

I think that since your problem relates to datatypes it might be worth
posting some of the variable declarations.

I am trying to create a unique number with the following.

Round((Rnd()*100000),0) & Format(Date(),"yymmdd") & [Region]![RegionID]

This create a number with 5 & 6 & 3 sig. figures (ie 14 digits) with
non
to
the right of the decimal.

I am trying to append this to a field of type decimal with precision 18
and
0 decimals.

It fails with conversion failure. I have tried ammending as below but
it
tell me I have the wrong number of arguments...

CDec(Round((Rnd()*100000),0) & Format(Date(),"yymmdd") &
[Region]![RegionID])

The help tells me it should be CDec(expression).

Any ideas....

Bruce

 

[JohnFol]

Interestingly, the help on CDec says:

The CDec function does not return a discrete data type; instead, it always
returns a Variant whose value has been converted to a Decimal subtype

Makes you wonder if you really need to use it, particularly if the field you
are inserting to is already defined . .

If you do, you could always return the value from a function in a module ie

=GetMyDec ([Region]![RegionID])

Function GetMyDec (RegionID)
GetMyDec = CDec(Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") &
RegionID)
End Function

If it helps anyone else identify the problem, I can simplify this greatly.
Just create an Append Query with the field being

=cdec(33)

Bruce I'll have a dig around

Thanks John,

I may have left some key info out. I am running this as a caclulated
field
in a query rather than from code. The query is an insert into action
query.

When I use CDec as in the example below the full error message is;
'The expression you have entered has a function containing the wrong
number
of arguments'

I have checked the properties of the destination field and they seem in
order.

Regards,

Hi Bruce, if I try and mimic what you have done in Access (using Access
data
types) I can get the following to work

Dim x As Double
x = Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333

Dim y As Double
y = 1 + x

and so does
y = 1 + CDec(x)

and so does
x = CDec(Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333)

I think that since your problem relates to datatypes it might be worth
posting some of the variable declarations.


I am trying to create a unique number with the following.

Round((Rnd()*100000),0) & Format(Date(),"yymmdd") &
[Region]![RegionID]

This create a number with 5 & 6 & 3 sig. figures (ie 14 digits) with
non
to
the right of the decimal.

I am trying to append this to a field of type decimal with precision
18
and
0 decimals.

It fails with conversion failure. I have tried ammending as below but
it
tell me I have the wrong number of arguments...

CDec(Round((Rnd()*100000),0) & Format(Date(),"yymmdd") &
[Region]![RegionID])

The help tells me it should be CDec(expression).

Any ideas....

Bruce

 

[Allen Browne]

Is this Access 2000? If so, it gives the error for FormatCurrency(),
FormatDateTime(), FormatNumber(), FormatPercent(), InStrRev(), MonthName(),
Replace(), Round(), StrReverse() and WeekdayName() functions, so it would
not be surprising to see it with CDec() also. Details:
http://support.microsoft.com/?id=225956

Access 97 also had serious problems with the Decimal data subtype.

Mind you, the problems are not fixed. Access 2002 and 2003 won't give you
this error, but they cannot sort the Decimal data type reliably. Details:
http://members.iinet.net.au/~allenbrowne/bug-08.html

As John Fol suggested, it would be better to use another data type.

On a different issue, Rnd() * 100000 will not necessarily give you a 5-digit
prefix.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks John,

I may have left some key info out. I am running this as a caclulated field
in a query rather than from code. The query is an insert into action
query.

When I use CDec as in the example below the full error message is;
'The expression you have entered has a function containing the wrong
number
of arguments'

I have checked the properties of the destination field and they seem in
order.

Regards,

Hi Bruce, if I try and mimic what you have done in Access (using Access
data
types) I can get the following to work

Dim x As Double
x = Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333

Dim y As Double
y = 1 + x

and so does
y = 1 + CDec(x)

and so does
x = CDec(Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333)

I think that since your problem relates to datatypes it might be worth
posting some of the variable declarations.

I am trying to create a unique number with the following.

Round((Rnd()*100000),0) & Format(Date(),"yymmdd") & [Region]![RegionID]

This create a number with 5 & 6 & 3 sig. figures (ie 14 digits) with
non
to
the right of the decimal.

I am trying to append this to a field of type decimal with precision 18
and
0 decimals.

It fails with conversion failure. I have tried ammending as below but
it
tell me I have the wrong number of arguments...

CDec(Round((Rnd()*100000),0) & Format(Date(),"yymmdd") &
[Region]![RegionID])

The help tells me it should be CDec(expression).

Any ideas....

Bruce

 

[Brendan Reynolds]

This is what I wrote in my blog about my attempts to get CDec to work in a
query ...

<quote>
The behaviour of the "CDec" function was also unexpected. When I attempted
to use it in the query, in the form "CDec([TestDouble])" I received the
following error message: "The expression you entered has a function
containing the wrong number of arguments."

The "CDec" function, when used in VBA code, takes one argument. But a little
experimentation soon showed that when used in the query, the function
expected two arguments. Given two numeric arguments, the function executed
without error in the query, but despite experimenting with various argument
values, it always returned the value 0.
</quote>

I never did get it to work, or figure out what that second, apparently
undocumented, argument is supposed to be.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks John,

I may have left some key info out. I am running this as a caclulated field
in a query rather than from code. The query is an insert into action
query.

When I use CDec as in the example below the full error message is;
'The expression you have entered has a function containing the wrong
number
of arguments'

I have checked the properties of the destination field and they seem in
order.

Regards,

Hi Bruce, if I try and mimic what you have done in Access (using Access
data
types) I can get the following to work

Dim x As Double
x = Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333

Dim y As Double
y = 1 + x

and so does
y = 1 + CDec(x)

and so does
x = CDec(Round((Rnd() * 100000), 0) & Format(Date, "yymmdd") & 333)

I think that since your problem relates to datatypes it might be worth
posting some of the variable declarations.

I am trying to create a unique number with the following.

Round((Rnd()*100000),0) & Format(Date(),"yymmdd") & [Region]![RegionID]

This create a number with 5 & 6 & 3 sig. figures (ie 14 digits) with
non
to
the right of the decimal.

I am trying to append this to a field of type decimal with precision 18
and
0 decimals.

It fails with conversion failure. I have tried ammending as below but
it
tell me I have the wrong number of arguments...

CDec(Round((Rnd()*100000),0) & Format(Date(),"yymmdd") &
[Region]![RegionID])

The help tells me it should be CDec(expression).

Any ideas....

Bruce

 

[Van T. Dinh]

It works fine for me without CDec() when I tested it in Access2002. Here
the SQL String that worked:

INSERT INTO Table2 ( DecField )
SELECT Round((Rnd()*100000),0) & Format(Date(),"yymmdd") & 1 AS Expr1;

Which Access version / SP are you using?