Combine values from multiple fields

[jsimard]

My database allows the user to enter "Reason for visit" and "Next Visit" up to 3 times each. A client can be seen for more than one reason. The next visit must be associated with the reason for visit:

Reason for Visit 1
Next Visit 1

Reason for Visit 2
Next Visit 2

Reason for Visit 3
Next Visit 3

Is there a way to take all of this and combine it some way so that I can query a "month" for next visit and anyone needing to be seen that month will display.

[Rhino373]

Well, reason to Visit appears to be a text field and Next Visit a Date/Time field. So you have a table with 3 fields whose headers are:

ID (this refers to your actual person)
Reason for Visit
Next Visit

If you were to query a month it would by default return all entries for where the criteria were the same.

[jsimard]

The next visit field is text not date/time as the user enter for example Aug 2011.

[jsimard]

I guess what I am wondering is if it is possbile to take Reason for Visit 1, Reason for Visit 2, Reason for Visit 3 and combine them all into 1 column?

[Rhino373]

You are looking to concatenate. See this example database from this post: http://www.access-programmers.co.uk/...ad.php?t=64611. In your use, you won't have field2 from their example. You can select design view on their qry2 and remove the second portion which shows field2.

[Rhino373]

Honestly though you need to reconsider your programming. There is no guaranteed way to sort by date on a hand entered field. One could type anything and you can't accurately group any together. An entry "Jul" would be different from "July". You'd need to convert this to a date or process some sort of validation against input

[jsimard]

The page is not found when I use the link.

[jsimard]

I have used an input mask on each field.

[Rhino373]

Sorry the link appeared to shorten itself. Try clicking here.