make Query type conversion error

[robojeff]

I have a form where I assign a value to a text field
and later attempt to dump this value to a table with a query but it will not dump the value to the table and bombs with a type conversion error.

The code that I use to set the field is:

Me.dsc = DLookup("[Desc]", "DUPtbl", "[Item] = Forms![DUPprint]!Item")

On the query, I have a field defined as:

Desc:forms!DUPprint!dsc AS [Desc]

This works fine as a select query but will not place this data into my table as a make query.

Do I need to somehow "type cast" this value in order to dump it into my table?

thanks

 

[Remou]

This seems a bit mixed up:

Desc:forms!DUPprint!dsc AS [Desc]

In the query window it should be:

Desc:forms!DUPprint!dsc

In SQL View it should be:

... forms!DUPprint!dsc AS [Desc] ...

Desc is a reserved word, and will cause you endless problems. I suggest you change the name of the field.

http://lessthandot.com

 

[robojeff]

Thank you Remou-

I tried this and per your recommendation, I also did change my table field name to Descr and due to a syntax error, I had to change

Descr:forms!DUPprint!dsc AS [Descr]
to
Descr: Forms!DUPprint!dsc = [Descr]

but I still get the error...

What I notice is that when my query makes the table, the field Descr is a binary field so it appears that the query is setting this table up incorrectly...


My query is as follows:

SELECT DISTINCT DUPtbl.[Work Order], DUPtbl.Item, forms!DUPprint!dsc AS Descr INTO tbl
FROM DUPtbl, tblCount
WHERE (((DUPtbl.Item)=[forms]![dupPRINT]![Item]));

Doesn't it seem like the query is setting the table field type up per the data that is stored in the dsc field? What is even stranger than that is if I change this query over to a select query then the query contains the descr field just fine... it is when it creates the table that the field type gets messed up causing the data conversion error...

I am not sure how to correct this...

 

[Remou]

I tried a similar query and it seemed to work fine, so, just guessing, try a conversion function, for example:

CStr(forms!DUPprint!dsc) AS Descr

http://lessthandot.com

 

[robojeff]

Thanks Remou-

I tried

CStr(forms!DUPprint!dsc) AS Descr

but I get Compile error: expected identifier

Is there a way to set the field on the form that I am pulling this data from "dsc" t a text field?

The only options that exist in the format option are number, date, currency. etc.. but no text field...

 

[Remou]

This is still a little strange. Please post the SQL from your query.

http://lessthandot.com

 

[robojeff]

My query is the same as my last post:

SELECT DISTINCT DUPtbl.[Work Order], DUPtbl.Item, forms!DUPprint!dsc AS Descr INTO tbl
FROM DUPtbl, tblCount
WHERE (((DUPtbl.Item)=[forms]![dupPRINT]![Item]));

 

[robojeff]

Ok, I was able to get the description to come through but I am not sure why except
I am thinking that this field had to be converted to a text field and nothing else was working
so I inserted a Trim statement into my query:

SELECT DISTINCT DUPtbl.[Work Order], DUPtbl.Item, Trim([forms]![DUPprint]![dsc]) AS Descr INTO tbl
FROM DUPtbl, tblCount
WHERE (((DUPtbl.Item)=[forms]![dupPRINT]![Item]));

And now I can get the description field added to my table...
very strange...

 

[Remou]

That is odd. I am glad you got it sorted out.

http://lessthandot.com