Unanswered: I have no idea what's goin on. Please HELP me!
I'm not an expert and I have no idea where the problem is so I'll try to explain everything:
I have made an application that is running on two different places.
I have split the database in two.
The back-end is absolutely the same (except for the data) and the only difference in the two front-ends are some default values in text boxes and value fields in combo boxes.
When I make a new query in one of the front-ends I just copy it in the other front-end and everything is fine (until now ).
Today I made a very simple union query that works fine in the first front-end. But when I copy it into the second front-end it doesn't work???
I have tried to export it, then to copy it and then to rewrite it to make sure everything is the same but it still doesn't work. I have even conected the two front-ends to the same back-end and the result is still the same - the first query works and the second does not work!
What is the problem?
The query looks like this:
SELECT Field1, Field2, FORMAT(Field3, "# - 00") AS Field3
UNION SELECT Field1, Field2, FORMAT(Field3, "# - 00") AS Field3
The source queries (Query1 and Query2) are the same in both front-ends and work fine even in the second front-end.
When executed, the union query in the second front-end gives the "UNKNOWN FORMAT" message.
When I remove the format of Field3 - the query works.
The problem is that I need that format (or to be exact - I need to separate the last two digits of that field. The first digits are registration number and the last two show the year of registration - like 123-03, 134-02 etc.).
So I have two questions:
1. What is wrong with that FORMAT
2. Why that query works in the first front-end and does not work in the second.
And finally HOW CAN I MAKE THIS QUERY WORK IN THE SECOND FRONT-END!
There is no set format of Field3 in the source queries.
They look like this: SELECT Field1, Field2, Field3 FROM Table1
Field3 has (# - 00) format set in the Table1.
Source queries display Field3 with this format but the union query does not. That's why I set the format FORMAT(Field3, "# - 00") AS Field3 in the union query.
And the question remains: WHY THAT QUERY WORKS IN ONE FRONT-END AND DOES NOT WORK IN THE OTHER. (The back-end is the same!)