Split Field based on criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings, I am having trouble wording the subject of question. In a
nutshell I am running a macro that exports a query to an excel file. The
query as currently done exports many fields including the field [amount]
which I would like to split based on a criteria [preferred].

[amount] is formatted as currency
[preferred] is formatted yes/no

I have tried to include two [amount] fields and have the criteria of one set
to [preferred] = yes and the other set to [preferred] = no.

the query return is empty. When I go back into design mode ACCESS has
re-written the query and removed the criteria I entered and replaced it with
"Yes And No" in the criteria for[preferred].

I am sure i am having a logic error somewhere but no amount of coffee is
helping me fix this. Any ideas.
 
Post your SQL statement. Open the query in design view. Click on menu VIEW
- SQL View. Highlight, copy and paste in post.
 
Access sometimes rewrites the query to help you. Sometimes it gets it
wrong... really wrong. So, when in doubt, use parens to designate what the
order of presendence should be.

a and b or c
a and (b or c)
(a and b) or c

When using the Query UI, it is possible to add OR condtions in multiple
rows. I would guess that you put too much on one row, causing the merger in
the rewrite.
 
Here is the SQL...I know why it didn't work. Coffee started kicking in. I
still haven't figured out how to make it display the way I want it to.

SELECT spend_data.[Vendor Name], spend_data.[Invoice Number],
spend_data.[Invoice Date], spend_data.GL_EFFEC_DT, spend_data.AU,
spend_data.ACCT, spend_data.Group, spend_data.Division, spend_data.[AU Name],
spend_data.[Manager Name], spend_data.[Group Head], spend_data.[Report ID],
spend_data.Catagory, spend_data.Preferred, spend_data.[Monetary Amount],
spend_data.[Monetary Amount]
FROM spend_data
WHERE (((spend_data.[Report
ID])=[Forms]![Reporting]![Combo_reporting_reportID]) AND
((spend_data.Catagory)="Copy Services") AND ([Preferred]=Yes) AND
([Preferred]=No))
ORDER BY spend_data.Preferred DESC;


KARL DEWEY said:
Post your SQL statement. Open the query in design view. Click on menu VIEW
- SQL View. Highlight, copy and paste in post.

H0MELY said:
Greetings, I am having trouble wording the subject of question. In a
nutshell I am running a macro that exports a query to an excel file. The
query as currently done exports many fields including the field [amount]
which I would like to split based on a criteria [preferred].

[amount] is formatted as currency
[preferred] is formatted yes/no

I have tried to include two [amount] fields and have the criteria of one set
to [preferred] = yes and the other set to [preferred] = no.

the query return is empty. When I go back into design mode ACCESS has
re-written the query and removed the criteria I entered and replaced it with
"Yes And No" in the criteria for[preferred].

I am sure i am having a logic error somewhere but no amount of coffee is
helping me fix this. Any ideas.
Click to expand...
Click to expand...
 
You don't explain what you mean by "splitting" the field [amount]. About
the only way I can think of splitting a currency value is into pounds
and pence.Can you give some examples of what the field might contain and
what you want to split it into.

If you mean you want the [amount] field to go into one of two fields
depending on the value of the field [preferred], use two calculated
fields in the query, like this:

Amount1: IIF([preferred], [amount], 0)

Amount2: IIF([preferred], 0, [amount])

Greetings, I am having trouble wording the subject of question. In a
nutshell I am running a macro that exports a query to an excel file. The
query as currently done exports many fields including the field [amount]
which I would like to split based on a criteria [preferred].

[amount] is formatted as currency
[preferred] is formatted yes/no

I have tried to include two [amount] fields and have the criteria of one set
to [preferred] = yes and the other set to [preferred] = no.

the query return is empty. When I go back into design mode ACCESS has
re-written the query and removed the criteria I entered and replaced it with
"Yes And No" in the criteria for[preferred].

I am sure i am having a logic error somewhere but no amount of coffee is
helping me fix this. Any ideas.
Click to expand...
 
Try this --
SELECT spend_data.[Vendor Name], spend_data.[Invoice Number],
spend_data.[Invoice Date], spend_data.GL_EFFEC_DT, spend_data.AU,
spend_data.ACCT, spend_data.Group, spend_data.Division, spend_data.[AU Name],
spend_data.[Manager Name], spend_data.[Group Head], spend_data.[Report ID],
spend_data.Catagory, IIf([Preferred]=-1,[Monetary Amount],"") AS [Preferred -
Yes], IIf([Preferred]=0,[Monetary Amount],"") AS [Preferred - No]
FROM spend_data
WHERE (((spend_data.[Report
ID])=[Forms]![Reporting]![Combo_reporting_reportID]) AND
((spend_data.Catagory)="Copy Services"))
ORDER BY spend_data.Preferred DESC;


H0MELY said:
Here is the SQL...I know why it didn't work. Coffee started kicking in. I
still haven't figured out how to make it display the way I want it to.

SELECT spend_data.[Vendor Name], spend_data.[Invoice Number],
spend_data.[Invoice Date], spend_data.GL_EFFEC_DT, spend_data.AU,
spend_data.ACCT, spend_data.Group, spend_data.Division, spend_data.[AU Name],
spend_data.[Manager Name], spend_data.[Group Head], spend_data.[Report ID],
spend_data.Catagory, spend_data.Preferred, spend_data.[Monetary Amount],
spend_data.[Monetary Amount]
FROM spend_data
WHERE (((spend_data.[Report
ID])=[Forms]![Reporting]![Combo_reporting_reportID]) AND
((spend_data.Catagory)="Copy Services") AND ([Preferred]=Yes) AND
([Preferred]=No))
ORDER BY spend_data.Preferred DESC;


KARL DEWEY said:
Post your SQL statement. Open the query in design view. Click on menu VIEW
- SQL View. Highlight, copy and paste in post.

H0MELY said:
Greetings, I am having trouble wording the subject of question. In a
nutshell I am running a macro that exports a query to an excel file. The
query as currently done exports many fields including the field [amount]
which I would like to split based on a criteria [preferred].

[amount] is formatted as currency
[preferred] is formatted yes/no

I have tried to include two [amount] fields and have the criteria of one set
to [preferred] = yes and the other set to [preferred] = no.

the query return is empty. When I go back into design mode ACCESS has
re-written the query and removed the criteria I entered and replaced it with
"Yes And No" in the criteria for[preferred].

I am sure i am having a logic error somewhere but no amount of coffee is
helping me fix this. Any ideas.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top