Access Query Design

  • Thread starter Thread starter Sunshine
  • Start date Start date
S

Sunshine

Pretty new to access but I really like building queries in the design view.
Is there a way to join a numeric field to a character field using the design
veiw? Originally I just went into the SQL view and add a STR() in front of
the numeric field but it didn't seem to like that.
 
Access / JET is fairly forgiving so you can concatenate a String with a
number and Access will type-cast the number to String automatically. Try:

[TextField] & [NumericField]
 
Pretty new to access but I really like building queries in the design view.
Is there a way to join a numeric field to a character field using the design
veiw? Originally I just went into the SQL view and add a STR() in front of
the numeric field but it didn't seem to like that.
Click to expand...

You can put almost any expression you like in a vacant Field cell in
the query design grid; for instance, if you have a numeric field N
(containing, say, 123) and a text field A (containing "ZYX") and you
want to see ZYX123, just put

SomeName: [A] & [N]

into a vacant field cell. You can also call the Str() function or
(even more flexibly) the Format() function in the same way - e.g. to
always display N with four digits with leading zeros, use

SomeName: [A] & Format([N], "0000")
 
I'm assuming by "join" you mean to find matching records
based on identical criteria, rather than "concatenate".

Attempting a join on fields of different types will
generate an error.

Create an intermediate query that returns the numeric
field as a string, and then join your text field to this
query. You'll need to trim the leading space that results
from the Str$ function. In the query design grid, create
a calculated field:

YourStringFieldName: LTrim$(Str$([numericfieldname]))

HTH
Kevin Sprinkel
Becker & Frondorf
 
Back
Top