Concatenation Multi Criteria - Concatenation in Microsoft Access


	Back to Concatenation Comments List

Upload Images @Reply Bookmark Link Email

Concatenation Multi Criteria

Ashlyn M Talley

3 years ago

Hello & humbled once again by "concatenation". Alright, I am able to now figure out, more it making near clear sense rather than just coping, pasting, & revising a single criteria in the domain functions. I am in need of multiple criteria for a Dlookup and have worn my computer beep near dead. To my credit I did get a simple multiple to work but for the life of me cannot get the multiples that I need.

Needing the following to be included:

[Document] = Forms!ApptDocCalendarF!Journey_Number
[Scheduled] = -1 (True)
[Completed] = 0 (false)

Field Types:
[Document] is Short Text
[Scheduled] is Yes/No
[Completed] is Yes/No
[Journey_Number] is Number

I'm probably either perhaps really close or way off with the following:

DLookup("ApptDate", "tbl_DocProgress", "Journey_Number =" & Forms!ApptDocCalendarF!Journey_Number AND"[Document] ='" & "Forms!ApptDocCalendarF!Journey_Number & " AND [Scheduled] = -1 AND [Completed] = 0)

Thank you in advance!

	Adam Schwanz @Reply 3 years ago
Missing some ambersands and quotation placements. Concatenation and Double Double Quotes `DLOOKUP("ApptDate","[tbl_DocProgress]","[Journey_Number]=" & Forms!ApptDocCalendarF![Journey_Number] & " and [Document]=""" & Forms!ApptDocCalendarF![Journey_Number] & """ AND [Scheduled]=True and [Completed]=False")`

	Alex Hedley @Reply 3 years ago
`DLookup("ApptDate", "tbl_DocProgress", "Journey_Number =" & Forms!ApptDocCalendarF!Journey_Number & " AND [Document] ='" & Forms!ApptDocCalendarF!Journey_Number & "' AND [Scheduled] = -1 AND [Completed] = 0")`

	Adam Schwanz @Reply 3 years ago
Alex have a quotation off, I did same thing the first time had to delete lol

	Alex Hedley @Reply 3 years ago
Where :p

Ashlyn M Talley OP @Reply

3 years ago

Hi and a big HUGE thanks to both Alex and Adam! You guys are AWESOME!!! I played and played with getting this syntax to work but fell short. Adam's version worked! Initially I was getting returned Null values only to find out that I had a field misplaced. I'll be sure to take apart and study this Concatenation as well as re-watching Richard's Tech Help. Thanks again guys, this is so friggin awesome! ~Ashlyn

Ashlyn M Talley OP @Reply

3 years ago

Sorry, to say I've got yet another... Feeling as though I'm never going to get this. I've watched the video links and afterwards feel the dots moving closer but I seem to be fooling myself.

If I get can get this or something similar to run, it updates all of the records in the table where it finds the DocOx variable. Or of course it asks me for the Journey_Number :(

SQLstring = "UPDATE tbl_Doc SET [" & DocOx & "]= True " & "WHERE [Journey_Number]='" & Forms!ApptDocPopF!Journey_Number & "'"

DocOx is a string variable
Journey_Number is a number

Scott Axton @Reply

3 years ago

Ashlyn -
Trouble shooting tips:
I've found that, for me, the easiest way is to start out in the Query Designer then switch to the SQL view.

If just doing SQL - try putting in "hard coded" values first and get the SQL working.
Then 1 by 1 change out to your variable. Testing in between each change to make sure it isn't broken.

MsgBox your SQLstring. That way you can see what the computer is returning and you can determine if you are missing spaces etc.

Ashlyn M Talley OP @Reply

3 years ago

Hello, thanks Scott for that pointer, what you describe would be a much more measured approach. I will both take your suggestion as well as continue to develop skill as far as where SQL meets VBA. Besides the Computer Learning Zone videos, found this one to also be helpful as well https://youtu.be/c2_fEdFBj_Q. If there are any others out there that you don't mind sharing, please by all means let me know. Thanks again. ~A

	Richard Rost @Reply 3 years ago
If you really want to learn this stuff, I highly recommend my SQL Seminar, at least Part 1.

	Scott Axton @Reply 3 years ago
Ashlyn - Just posted yesterday! Check out the Tasks extended cut for a perfect example of what I mentioned above on building your SQL. It happens at about the 8:45 mark. BTW that is a true statement from Richard above. The SQL Seminar is GOLD in my opinion.

Ashlyn M Talley OP @Reply

3 years ago

Hi both Scott and Richard, thank you so for NOT providing me with the solution to my statement. After a bit of time, watching and re-watching videos, coupled with the "Task" extended cut... I figured out and wrote my Where Clause that had multiple criteria one being a date. Still find it a bit challenging but at least now it's starting to make a LOT more sense. Such powerful stuff, I'll take a look at the SQL Seminar to see what planets and stars I can further speak into existence. Thanks again!!!

	Scott Axton @Reply 3 years ago
Thanks - I think... I kind of work on the old theory: You feed a person a fish, the hunger goes for a day, but teach them TO fish and it goes for a life time.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Concatenation.