Exporting Query to Excel

[bapster]

I have a command button that executes a query and sends it to MS Excel.

Once there, I am trying to convert it to a IIF format to Import it into Quickbooks. I already have the template designed for this. But it requires
certain data to be in certain columns.

My question is, I want the Excel Sheet that is created from the query to
place data in the following colums A,B,C,D,E,G (notice F needs to be blank)

I have tried to insert a BLANK in my original query, but when you save it, the BLANK column in the query is removed. I suppose I could make a dummy field and leave it blank....and add it in the 5th column of the Query..

If I made myself clear, can anyone offer a tip tp do this?

Thanks.

 

[Mike375]

Yes, you could make another field in your table.

The other thing you can do is to insert a blank column in the query like you did previously but the you have to create data for that blank field in the query.

Let's say you call this created field MakeBlank

Then on the line where field names in the query appear type

MakeBlank:IIf([SomeField] Like "Z",Null,Null)

SomeField can be any field you have dragged down from your table and the new field MakeBlank will always be blank.

For your future reference the IIf is for producing True or False results.

So you might have IIf([SomeField]>100,"Yes","No)

That might be used to say if a discount will apply when the order exceeds 100

Or it could be IIf([SomeField]>100,[Price]*.9,[Price])

In that case the newly created field would show a figure of 90% of the price when SomeField is greater than 100 and it would show the actual price when SomeField is less than 100 or less

Mike

 

[namliam]

to insert a blank field you dont need a complex thing like that...

Simply...

BlankField:Null

Will be enough

BR

 

[bapster]

That solved it...

Thanks, that simple BlankField:Null did the trick....

It's good to have forums like these to learn from the
more experienced!

The only problem I have now is when the fields are
transferred into Excel, the cells change size (row height expands)
I suppose because of the length of the field (I don't care about the column
width, but I don't want the row height to change like it does)

Any quick way to prevent the cells height from changing?

Thanks so much for the quick tip.

One problem solved!

Phillip

 

[namliam]

It because of wrap text... in you format (default for access exports)

You could automate it and remove it or do it by hand...

Format => Cells => (2e tab)

Not sure on the translation (i am translating from dutch )

Regards

 

[Mike375]

to insert a blank field you dont need a complex thing like that...

Simply...

BlankField:Null

Will be enough

BR

My thoughts were that he learnt the IIF for Access then that would be a good thing.

Mike

 

[Pat Hartman]

There is no need to export to Excel prior to exporting the file you need for Quickbooks. You can export the file directly from Access. Just make a query that selects the columns in the correct order and formats them if necessary.