Problem w/ Relationship 1

[ohmbru]

I have 2 databases that use similar data. Most of the time the datatypes match. I can't create a relationship I need in my query.

The field is PolicyNumber. In database 1, the field is imported as a number (1234567). In database 2, as text(1-234567). The source for DB 2 includes a dash (-) which is why it is a text field.

I now import DB 1 PolicyNumber as a text field and input mask 0\-000000. This still does not allow me to make the relationship in my query. Apparently, the dash in DB 1 is not recognized.

Any thoughts on how to crack this nut?

Thanks,
Brian

 

[igk]

Try to import PolicyNumber without changes to the data but as text and then update the field as:
"0"&"-"&"[PolicyNumber]"

 

[ohmbru]

This is along the lines I was thinking, but it always returns a zero in the first position, then the full 7-didgit number (0-1234567). Is there a way to isolate the first position of the field in an expression like this?

Brian Brian

 

[igk]

Where does the first part of the PolicyNumber comes from?

 

[ohmbru]

The PolicyNumber is stored in a .txt file like this: "1234567"

I may be successful if I could break up this field like you suggest.

[PolicyNumber] & "-" & [PolicyNumber]
1st position positions 2-7

But I don't know how to define the parts of the field. Brian

 

[ohmbru]

...to get "1-234567" Brian

 

[igk]

Check those 2 functions: Left(string, length) and Mid(string, start[, length])

 

[ohmbru]

That worked. I still can't make the relationship but I think I will have to make a table first.

Thanks for your speedy help!

Brian