Make Table Number Field Issue!!

[Clarkie001]

Hi,

I have a field within a make table query which does a dlookup up on another table.

The issue is, when I run the make table query, this field is returned as Text when it should be a number. This is causing issues, as I wish to use this newly created table in another link.

Does anyone have any ideas, thanks in advance.


Clarkie

 

[danvlas]

DLookup returns a variant(string).
Val(DLookup(...)) should turn it into a number.
Val(Nz(DLookup(...), 0)) should take care of nulls and turn them to zeroes.

And another name for Make-Table queries is 'Make-Trouble'
See if you really need it, if not, dump it.

HTH





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Clarkie001]

Daniel,

Its the simple things in life that make this job easier....thanks.


Clarkie

 

[Clarkie001]

Daniel,

Its is now returning as number, but as double is there anyway of changing that to Interger?


Clarkie

 

[danvlas]

Try Int(Val(DLookup(...)))

But I'm not sure it works...

You could do something else:
Design the table as you want it, then:
- run a delete query to remove all records
- run an append query to put the records in the table (simple change of your Make-Table into an Append)

Performance stays the same (slow, database size increases), but at least you have the fields defined as you want them.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Clarkie001]

Cheers,


Clarkie