Formatting a concatenated field

[kris335]

I have two auto sequential number fields that are formatted to be 3 digits long (such as 001 and 123). I have a third number field that combines those 2 numbers (i.e. 021-101). However, I cannot get the formatting to work in the 3rd field. It always reverts to 1-1 for the first record (or 2-2 for the 2nd), when I want it to say 001-001.

I have it formatted in my property sheet as 000-000 and I also tried the Format function but couldn't get it to work correctly. The name of the combined field is Child Code, so my function was: Format([Child Code], "000-000") but I kept getting an "operand without an operator" error.



Any help would be greatly appreciated!

[Robeen]

Quick question:
Are all these fields in a Table?

Have you considered creating your concatenated field in a query instead?

The reason I ask is that if this is in a table, you will be storing data that is already IN the table [your two number fields] into another table field thereby duplicating data.

[Robeen]

If you want to try the Query then here's an example:

 
SELECT Table1.FirstNumber, Table1.SecondNumber, Format([FirstNumber],"000") + "-" + Format([SecondNumber],"000") AS JoinedNumber
FROM Table1;

[kris335]

This is where it gets complicated. My first 3-digit number is called "Family-App ID" and it is from a table called "Parents". My second 3-digit number is called "Child ID" and it is from a table called "Child". So yes, my initial 3 digit codes are from a table.

I have tried combining those fields in the table to display onto a form as well as just having an unbound text box on my form with my combined numbers. Either works but neither formats it properly.

[Robeen]

Which Table are you trying to combine the numbers from the two different fields in??

With the information you provided, you could have a query something like this [untested]:

SELECT Parents.[Family-App ID], Child.[Child ID], Format(.[Family-App ID],"000") + "-" + Format([Child ID],"000") AS JoinedNumber
FROM Parents, Child
Where Parents.[JoinField] = Child.[JoinField];

The above assumes that you have fields called 'JoinField' in both the tables.

Let me know if this helps.

[kris335]

Since the 2 joined numbers are from 2 different tables, which table would be the FROM table? And how do I get the "-" in there?

Currently looks like this:
SELECT Parent.[Family-App ID], Child.[Child ID], Format([Family-App ID],"000")+Format([Child ID],"000") AS JoinedNumber
FROM Table1;

Thanks for the help.

[kris335]

Posted that last one before I saw your reply. Going to add what you suggested!

[kris335]

Should I create the JoinField fields in both my tables?

[Robeen]

There has to be a way to tell Access which field in Parent to join to which field in Child.

If you want the [Family-App ID] and[Child ID] fields to be the joined fields - you can do this:

SELECT Parents.[Family-App ID], Child.[Child ID], Format(.[Family-App ID],"000") + "-" + Format([Child ID],"000") AS JoinedNumber
FROM Parents, Child
Where Parents.[Family-App ID] = Child.[Child ID];

If you do - Access will look for a match in those fields between Parent & Child. So - the 001 in Parent will join to the 001 in Child.

Let me know if that works.

[kris335]

I have the query as my row source but when I go to open my form, it says it can't find a query named that and to check my record source. I can't find a property for "record" source.

[kris335]

SELECT Parents.[Family-App ID], Child.[Child ID], Format(.[Family-App ID],"000") + "-" + Format([Child ID],"000") AS JoinedNumber
FROM Parents, Child
Where Parents.[Family-App ID] = Child.[Child ID];

This is giving me a syntax error where it says to enclose the subquery in parenthesis. Which is the subquery?

[Robeen]

So - you created the query - and it works?

Then - you went into Form Properties and Changed where your Form is getting its data from? How did you do this?

If you open your Form, Open the 'Property Sheet' and Click the 'Data' Tab - you should see a 'Record Source' Property that has a drop-down arrow from which you can select your query from the list.

[Robeen]

you have a syntax error in there - my bad - sorry. There was a '.' in the 'Format(.[Family-App ID],"000")

Try this:

 
SELECT Parents.[Family-App ID], Child.[Child ID], Format([Family-App ID],"000") + "-" + Format([Child ID],"000") AS JoinedNumber
FROM Parents, Child
Where Parents.[Family-App ID] = Child.[Child ID];

[kris335]

When I make the record source the query, it messes up the rest of the form. Can I make the query the source for just the "Child Code" field?

[Robeen]

How is it messing up the rest of the Form?

It IS possible to get the value for 'Child Code' from the query - but it will involve writing VBA code.

Is your Form a very complicated one? If not - you might just re-build it using the query as the data source.

OR - try this:

Make sure that your query has all the same fields that your Form has.
Then -
For each field on your Form -
Select the field -
Go to its Property Sheet - Data tab -
Make sure the Control Source is one of the available fields from the drop-down list.