asked on

Type mismatch in expression - simple but urgent access query

I am attempting to create a reasonably complex SQL query within MS Access. I'll explain what i'm trying to achieve first, including table and column names, then i'll show you the non-functioning query I have:

I have 2 tables. One called StudentStatus which contains non-primary columns; BuiltDate (Date/Time) and BuildNo (Number). The other table is called dbo_builds_progress_tbl and has the columns serial_num (Number) and a Date/Time column called end_d.

I wish to take values from end_d and put them into or update BuiltDate where a value in BuildNo is equal to a value in serial_num. So the only values that will change will be BuildNo within StudentStatus and only if there is a match between the BuildNo in StudentStatus and the serial_num in dbo_builds_progress_tbl - in which case it will update the BuiltDate to the corresponding end_d

I explained that twice, I know.

I have produced this query to do it:

UPDATE StudentStatus, dbo_builds_progress_tbl SET StudentStatus.BuiltDate = dbo_builds_progress_tbl.end_d
WHERE StudentStatus.BuildNo = dbo_builds_progress_tbl.serial_num;

This produces the error "Type mismatch in expression"

Help appreciated

Guy Hengel [angelIII / a3]

is any of the 2 tables a linked table (I assume db_builds_progress_tbl is such a linked table).
in case that is so, what data type is the field serial_num in the underlying database?

wbstech

ASKER

Ah - no linked fields but I just realised BuildNo is a text field and serial_num a number field. This is because although BuildNo mainly contains numerical entries, there are some that have the entry "n/a" and similar.

Is there an easy way around this?

wbstech

ASKER

I now have the following query:

UPDATE StudentStatus, dbo_builds_progress_tbl SET StudentStatus.BuiltDate = dbo_builds_progress_tbl.end_d
WHERE (((StudentStatus.BuildNo)=CStr([dbo_builds_progress_tbl].[serial_num])));

This produces "Operation must use an updatable query"

Any ideas?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wbstech

ASKER

Ok - Did that by making 2 separate queries creating a conversion inbetween. Thanks =)