[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trying to run an update query

Posted on 2014-08-13
11
Medium Priority
?
118 Views
Last Modified: 2014-08-14
I have an update query design with the source and the destination tables in the query designer.  But the joined fields are a text field in one table and a number field in the other table.   So naturally I'm getting a type mismatch error.  Is there some way to convert the text field to a number?  It will always truly be a number.

I tried ID: Val([textfieldname) but that didn't work.
0
Comment
Question by:SteveL13
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40259183
Try CInt(txtfield) if it is an integer you're joining to

If a long integer use CLng(txtfield)
or a double
CDbl(txtfield)

These will fail howver - if there are ANY non numeric values in the column.


Kelvin
0
 
LVL 85
ID: 40259184
Try using one of the conversion functions:

ID: CDbl(YourTextField)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40259191
"Tried" how exactly...?
Post the SQL you tried...

try: cdbl(YourTextField)
...if you need decimals

...or clng() if you only have whole numbers
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40259193
You can ignore my post...
other experts were there first..
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40259229
If you have control of the tables, the correct solution is to fix the data type discrepancy.  Otherwise, one the various type conversions already suggested will work.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40260068
Don't use Join but Where:

Select
   tblFirst.*,
   tblSecond.*
From
   tblFirst,
   tblSecond
Where
   tblFirst.IdNumeric = Val(tblSecond.IdText)

- or:

Where
   Trim(Str(tblFirst.IdNumeric)) = tblSecond.IdText

/gustav
0
 

Author Comment

by:SteveL13
ID: 40260526
I just tried :

Select
    tblFirst.*,
    tblSecond.*
 From
    tblFirst,
    tblSecond
 Where
    tblFirst.IdNumeric = Val(tblSecond.IdText)

But I get an error indicating that I can't use asterisks and that I need to add each field to the grid.

What should the code look like?  I have 13 fields in total.
0
 

Author Comment

by:SteveL13
ID: 40260574
Ok.  I have attached a file that I created by stripping everything else out of the real database.  I cannot get the update query to work because I'm sure I'm doing something wrong.  Can someone help?

Notes:

The ID field in tblNotes is numeric and has to be.
The ID field in tblTEMPORARY is text and has to be.

--Steve
Update-Qry-Example.accdb
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40260702
It does matter which field you apply VAL on.
This is how:

WHERE Val(tblTEMPORARY.NotesID)=[tblNotes].[NotesID];

/gustav
0
 

Author Closing Comment

by:SteveL13
ID: 40260773
BINGO!!!  Thanks so much.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40260853
You are welcome!

/gustav
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question