Importing and Field Types

[MarcusAntonius]

I currently import data from a csv file into a table(A), which is then copied into table(B). In table A the fields are of type text since it's from a csv file. The fields in table B are also of the same type. If I change fields in table B to 'number' for example, then I can't copy the data from table A due to a type mismatch. Since I need to change the types in order to perform various calculations, is there any way around this?

 

[KenHigg]

Can you do an append query and use val() to convert the text on the fly?

ken

 

[freda42]

Can you do an append query and use val() to convert the text on the fly?

How would you do that? I've only just learnt about append queries, but have the same problem with data types. I've ended up converting all my data to text, but some of it seems to get lost when I change it back again.

thanks!

 

[KenHigg]

See screen shot:

From an append query where field_1 is the table appending from and field_2 is in the table you're appending to...

???
ken

 

[MarcusAntonius]

Where do I define what type I want it changed to? i.e. text to number, date, etc.

 

[KenHigg]

val() converts a string to a number.

???
ken

 

[MarcusAntonius]

Ah right. Thanks for the info, much appreciated.

 

[MarcusAntonius]

How do I apply this to an update query?

I have a field called days, which contains '*' as well as numbers. Can I convert them to blank or '0' like below?

Field: Days
Table: A
Update to: [table b]!days
criteria: val("stringexpr")

 

[KenHigg]

Instead of bring down the field you want to convert into the append query, put something like: 'expr1: val(mytextstring)'. The query will then place the results from this into the number field you select.

???
ken

 

[KenHigg]

Sorry that was append - but it's similar - Need more help?

ken

 

[MarcusAntonius]

I have set the update query like this:

Field: Days
Table: A
Update to: [table b]!days
criteria: val("stringexpr")

However, this changes the '0' back to '*'

 

[KenHigg]

I thought we were doing an append (convert it as it comes over). Your example looks like an update (converting it after it comes over).

???
ken

 

[MarcusAntonius]

Sorry for muddling the issue.

Yes, the first was append and with your help that worked beautifully.

Now I want to apply the same to an update query.

 

[KenHigg]

Then only one table to deal with. (I think this will work...)

See pic...

???
ken

 

[MarcusAntonius]

In the example, it would have to be

Field: Days
Table: Table A
Update to: val([[table b]!days])

since it is updating from table B to table A, but this results in an expression with invalid syntax.

 

[KenHigg]

Have you brought the other table into the query and established the relationship?

ken

 

[MarcusAntonius]

Yes I have done that.

 

[KenHigg]

val([[table b]![days])


???
ken

 

[MarcusAntonius]

Super! It worked a treat!

Thank you for your help!

 

[KenHigg]

Cool...

(Whew. That was like pulling teeth)