Avatar of wbstech
wbstech

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
SQL

Avatar of undefined
Last Comment
wbstech
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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?
Avatar of wbstech
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?
Avatar of wbstech
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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of wbstech
wbstech

ASKER

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

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo