Search Criteria for Concatenated Fields?

[schulzy175]

I have a field that is concatenated with 4 other fields [LOGDATE], [FIRSTNAME], [LASTNAME], [ITEM]. I am trying to sort out [LOGDATE] with the "to and from" textboxes from my form SearchF. I cannot seem to find a solution other than the DLookup function. I have tried the DLookup function, but can't get it to work and I know I have an error, but can't find the solution as to where. Here is my current code:

Dlookup ("[LOGTIME]", "[SALE]", "[LOGTIME] between [Forms]![SearchF]![fromdate] and [Forms]![SearchF]![todate]")

Logtime is the field name located in the table "Sale". Thanks in advance!

EDIT:

Dlookup ("[LOGDATE]", "[SALE]", "[LOGDATE] between [Forms]![SearchF]![fromdate] and [Forms]![SearchF]![todate]")

[June7]

What has the concatenated value to do with this?

I have seen Access resolve references to form controls when they are within quotes so maybe just need # delimiters.

Dlookup ("[LOGDATE]", "[SALE]", "[LOGDATE] between # [Forms]![SearchF]![fromdate] # and # [Forms]![SearchF]![todate] #")

or concatenate:

Dlookup ("[LOGDATE]", "[SALE]", "[LOGDATE] between #" & [Forms]![SearchF]![fromdate] & "# and #" & [Forms]![SearchF]![todate] & "#")

Where exactly are you using this expression? DLookup will just return the first LOGDATE value it encounters that meets the criteria.

[schulzy175]

What has the concatenated value to do with this?

I have seen Access resolve references to form controls when they are within quotes so maybe just need # delimiters.

Dlookup ("[LOGDATE]", "[SALE]", "[LOGDATE] between # [Forms]![SearchF]![fromdate] # and # [Forms]![SearchF]![todate] #")

or concatenate:

Dlookup ("[LOGDATE]", "[SALE]", "[LOGDATE] between #" & [Forms]![SearchF]![fromdate] & "# and #" & [Forms]![SearchF]![todate] & "#")

Where exactly are you using this expression?

In the criteria field on query design. Thank you for the response by the way.

[June7]

Using DLookup like that makes no sense if there could be multiple values within the date range. Post the complete query SQL statement. Sample of raw data would also be helpful.

[schulzy175]

It will be tough since the database is proprietary, but is there anything else I can get to help? I have 4 fields combined into one field labeled "Description", but I am wanting to sort "Description" by the [LOGDATE]. DLookup was the only function that any google result showed.

[June7]

Why? Why don't you just include LOGDATE field in the query and apply filter to it?

Don't really need your proprietary data (and that would be what - names and addresses?). Mock up a table in the post that recreates the table structure but don't use your proprietary data. Use the Advanced Edit window tools. And an SQL statement does not reveal data.

[orange]

Databases work very well on the 1 concept 1 field design approach. Why exactly did you concatenate 4 fields?

[schulzy175]

I am designing an export query specifically for importing into quickbooks as invoices. I have to add 4 fields together because you can only import excel into quickbooks with the exact same layout quickbooks uses and in order to add multi-line items into quickbooks, the data has to be setup in a very specific way. The customers sale date, name, and item purchased are all in one description because even though the sale dates are different, they are with the same account so I have to make sure that there is information to let quickbooks know it is a multiline item.

SELECT DateSerial(Year(Date()),Month(Date())+1,0) AS [INVOICE DATE], [LOGDATE] & " " & [FIRSTNAME] & " " & [LASTNAME] & " " & [ITM.NAME] AS Description, SALEITMS.QTY, [Invoice Class].Class, SALE.TOTALFROM [Invoice Class] INNER JOIN ((SALEITMS INNER JOIN ITM ON SALEITMS.ITEM = ITM.OBJID) INNER JOIN ((SALECHGS INNER JOIN ACCT ON SALECHGS.ACCTNUM = ACCT.OBJID) INNER JOIN ((CUST INNER JOIN CUSTCDE ON CUST.OBJID = CUSTCDE.CUST) INNER JOIN SALE ON CUSTCDE.OBJID = SALE.CUSTCDE) ON (SALECHGS.STE = SALE.STE) AND (SALECHGS.SALEID = SALE.OBJID)) ON (SALEITMS.SALEID = SALE.OBJID) AND (SALEITMS.SITE = SALE.SITE)) ON ([Invoice Class].Site = SALECHGS.SITE) AND ([Invoice Class].Site = SALE.SITE)
GROUP BY ACCT.NAME, SALE.CREATED, DateSerial(Year(Date()),Month(Date())+1,0), [LOGDATE] & " " & [FIRSTNAME] & " " & [LASTNAME] & " " & [ITM.NAME], SALEITMS.QTY, [Invoice Class].Class, SALE.TTL, ITM.NAME
HAVING (((ACCT.NAME)=[Forms]![SearchF]![actname]) AND ((SALE.CREATED) Between [Forms]![SearchF]![fromdate] And [Forms]![SearchF]![todate]));

I have hopefully masked well enough the information that I feel is sensitive, but it is the best that I will be able to do in terms of providing the SQL.

[schulzy175]

Found a work around. Instead of trying to filter Logdate inside of the concatenation, I just created a new query that filtered the logdate and then put that new query into the concatenation in place of logdate. I apologize for the minute amount of background info, but I appreciate all the help. Ill be marking this as solved for now.

[June7]

BTW, Name is a reserved word and should not use reserved words as names for anything. Also, advise to avoid spaces in naming convention, even in alias names, so INVOICE DATE would be better as InvDate or INV_DATE.

[schulzy175]

BTW, Name is a reserved word and should not use reserved words as names for anything. Also, advise to avoid spaces in naming convention, even in alias names, so INVOICE DATE would be better as InvDate or INV_DATE.

Notated 👍 Thank you.