Re: correct way to write the syntax for a Dlookup in a query expression in VB
- From: DeZZar <derrick.goostrey@xxxxxxxxx>
- Date: Mon, 28 Sep 2009 16:24:27 -0700 (PDT)
On Sep 28, 8:02 pm, lyle fairfield <lyle.fairfi...@xxxxxxxxx> wrote:
On Sep 28, 3:45 am, DeZZar <derrick.goost...@xxxxxxxxx> wrote:
Hi All,
I am attempting to convert a functioning query into VB code. I am
using a Dlookup as a formula in the query to determine if newly
imported records already exist in another table. I want to take only
those that return false when the query runs to another table and leave
the remainder behind to be dealt with by some subsequent rules....
So my expression in part looks like this
INSERT INTO - Blagh blagh blagh
SELECT - etc etc etc
FROM - this table here......
WHERE (((IIf((DLookUp
("[QuoteNumber]","[tbl_CurrentValidations]","[QuoteNumber]=
[tbl_DailyDataImport].[LQSQuote]"))>0,'True','False'))='False'))
Of course, VBA does like all the "" in the Dlookup function when it is
part of the SQL statement for the Query, but I have attempted a
combination of ' instead of " etc and haven't had much luck.
Are there some basic conversion rules I need to follow here?
Cheers
DeZZar
I suggest:
a) that you do not have two tables with the same structure;
b) that you guard against duplicate entries by creating a unique
index;
c) that you rewrite your sql select as a join checking for nulls
(we could help with that is we were swift enough to understand the
DLookup; I am not).
Others will suggest that you do not use domain functions in SQL. I
will suggest that you do not use domain functions.
Lest you become prickly, please, remember that your question and all
the thread generated by it may be shared by many.- Hide quoted text -
- Show quoted text -
not prickly at all. I am no expert at access so any advise in a
different direction is helpful.
I have two tables with a similar structure (not the same) becuase
another system pulls down information from a mainframe every morning
and deposits it as excel file. I am building this DB to import all
the new information from the excel sheet daily - but it must compare
the new data with what already exists in the database. So I import
into one table, assign a True/False in a new column based on the
Dlookup and then run further proceedures to split them up based on the
true false.
I've done it this way becuase:
a) the duplicated records cannot simply be discarded (based on Index
rules etc)
b) the duplicates may include differing information in particular
fields and the user needs to review the list and decide to either
accept the changes, or to discard and proceed.
I am building a "processing" screen which will a) import new records
b) review them (split between new and duplicated) c) have the user
review the duplicates and either accept or discard changes and d)
allow user to accept all which moves all records to the main table,
empties out the "import" table and processes each record in a loop to
create emails, system folders, generated documents and additional
field information....this all happens at the push of a button....
If anyone has a better idea please share! My ideas are based on my
limited knowledge of sql, vb and access in general! For instance, I
had no idea NOT EXISTS was a function that I could use in SQL!
Cheers
DeZZar
.
- Follow-Ups:
- References:
- Prev by Date: Re: Query design question
- Next by Date: Re: correct way to write the syntax for a Dlookup in a query expression in VB
- Previous by thread: Re: correct way to write the syntax for a Dlookup in a query expression in VB
- Next by thread: Re: correct way to write the syntax for a Dlookup in a query expression in VB
- Index(es):
Relevant Pages
|
