Re: deleting columns in table

Depends on what you mean by "empty". Usually "empty" equates to Null, but
it's possible that it could be an empty field ("" or " ")

Since you only want rows where data exists, you'd want something like:

INSERT INTO TempTable (Field1, Field2, Field3, ...)
SELECT Field1, Field2, Field3, ...
FROM MyTable
WHERE Field1 IS NOT NULL
OR FIeld2 IS NOT NULL
OR Field3 IS NOT NULL
....


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"witek84@xxxxxxxxx" <witek84gmailcom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:AFA6A45F-431D-414E-9847-DFCB6435C1D3@xxxxxxxxxxxxxxxx
Yes but how choose empty column using query?

"Douglas J. Steele" wrote:

All you need is two queries, no code at all.

To delete the contents of the temp table, you need a Delete query:

DELETE * FROM TempTable

To repopulate the temp table with data from an existing table, you need
an
INSERT INTO query:

INSERT INTO TempTable (Field1, Field2, Field3, ...)
SELECT Field1, Field2, Field3, ...
FROM MyTable

If there's data in MyTable that you don't want inserted into the temp
table,
put an appropriate WHERE clause.

In general, any time you have to choose between using code and using a
query
to do the same thing, always choose the query.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"witek84@xxxxxxxxx" <witek84gmailcom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:89EE5839-43D0-4014-8E24-2D5D2BC960B0@xxxxxxxxxxxxxxxx
I want to delete in macro empty columns.
I've got 2 queries. First deleting all data in temp table. Second serch
all
records in table that have minimum one cell with data. that query put
data
into temp table. But not every colums have data. I want that my macro
check
column and if in column we don't have data then will delete. I've
creating
macros only in excel and Í don't know how write this macro. I know only
this:

dim val as integer

for x=1 to 10 'I have 10 columns in table
val=0
for y=1 to eof 'how is end of records in file in access??
if cells(x,y)<>empty then 'in excel I'm using cells I don't
know
in access is that same
val=val+1
end if
next j
if val<>0 then
'and now I want to delete column x
end if
next i

Thanks








.

Relevant Pages

  • Re: Executing make-table queries with VBA
    ... You're running the query that creates the table, ... you'll run into the same problem using a macro. ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.modulesdaovba)
  • RE: Access and Excel 2007 - 65000 rows limitation trouble
    ... Table name (the query will work here too); Field Name (what you want to call ... Save the macro and name it. ... Now just run the macro to export the data to Excel. ... Jerry Whittle, Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • Re: deleting columns in table
    ... Doug Steele, Microsoft Access MVP ... To delete the contents of the temp table, you need a Delete query: ...
    (microsoft.public.access.modulesdaovba)
  • Re: deleting columns in table
    ... All you need is two queries, ... To delete the contents of the temp table, you need a Delete query: ... I want that my macro ...
    (microsoft.public.access.modulesdaovba)
  • Re: Email a report from a form for a single record
    ... the main problem I see here is with the 'Where Condition' argument in the macro. ... you already have the report restricted to the one record via the query criteria. ... There is probably no need to define a Parameter in the query like that. ... Steve Schapel, Microsoft Access MVP ...
    (microsoft.public.access.macros)
Loading