DateRange

[fixious]

I have a text box that I want enter in a firing date. If this date is between Jan 1 - Jun 30 then I want the expiration date to be Jun 30 but if its between Jul 1 Dec 31 then I want the expiration date to show Dec 31. Is there a way I can do this?

 

[mgolla]

you could try:

ExpirationDate = IIf(DateDiff(&quot;d&quot;, &quot;1/1&quot;, Format(Text0, &quot;mm/dd&quot) > 0 And DateDiff(&quot;d&quot;, &quot;6/30&quot;, Format(Text0, &quot;mm/dd&quot) <= 0, &quot;6/30&quot;, &quot;12/31&quot

 

[fixious]

Thanks I'll give it a shot. I have many more small problems like this I still stumped on buy I don't want to hoard your time because Im learning slowly but if you want to answer them please and thank you.

1) Biggest problem of all- I have a query based on all of the shot due dates and Name/SSN/Section. I think I have it set so the shot due dates are within 30 days using the Between Date function. The problem is when one shot is due it brings up all the other shots for that individual. In a perfect world I would like to only see the Name/SSN/Section and Shot due and due date. Multiple names for another shot on same report is perfect. Then I can sort by due date so basically there may be the same person in the report a couple times if their shots fall within the next 30 days. The other problems I have like I said are small but this one has been the killer and I haven't got anywhere but a query of all shots. Thanks for you time and assistance in this matter and thanks again for the code,I'll check it out.

 

[mgolla]

fixious,

If you post your code I can take a look at it and see what I can do to help you out

 

[fixious]

You want the code for my table? Newbie here..sorry

 

[mgolla]

Actually, I meant for you to let me see the SQL for your query on the shots

 

[fixious]

SELECT Individual.Name, Individual.Rank, Individual.Flight, Individual.Anthrax, DateAdd(&quot;yyyy&quot;,10,[Yellow Fever Taken]) AS [Yellow Fever], DateAdd(&quot;yyyy&quot;,10,[Tetanus Diptheria Taken]) AS [Tetanus Diptheria], DateAdd(&quot;m&quot;,6,[Yellow Fever Taken]) AS [Hepatitis A], DateAdd(&quot;d&quot;,180,[Hepatitis B Taken]) AS [Hepatitis B], DateAdd(&quot;yyyy&quot;,2,[Typhoid Taken]) AS Typhoid, DateAdd(&quot;yyyy&quot;,1,[Influenza Taken]) AS Influenza, DateAdd(&quot;yyyy&quot;,5,[Menomune Taken]) AS Menomune, DateAdd(&quot;yyyy&quot;,1,[TB Skin Test Taken]) AS [TB Skin Test], DateAdd(&quot;yyyy&quot;,0,[Polio Taken]) AS Polio, DateAdd(&quot;yyyy&quot;,10,[Smallpox Taken]) AS Smallpox, Individual.[Measles, Mumps, and Rubella Taken]
FROM Individual
WHERE (((Individual.Anthrax) Between Date() And Date()+30)) OR (((DateAdd(&quot;yyyy&quot;,10,[Yellow Fever Taken])) Between Date() And Date()+30)) OR (((DateAdd(&quot;yyyy&quot;,10,[Tetanus Diptheria Taken])) Between Date() And Date()+30)) OR (((DateAdd(&quot;m&quot;,6,[Yellow Fever Taken])) Between Date() And Date()+30)) OR (((DateAdd(&quot;d&quot;,180,[Hepatitis B Taken])) Between Date() And Date()+30)) OR (((DateAdd(&quot;yyyy&quot;,2,[Typhoid Taken])) Between Date() And Date()+30)) OR (((DateAdd(&quot;yyyy&quot;,1,[Influenza Taken])) Between Date() And Date()+30)) OR (((DateAdd(&quot;yyyy&quot;,5,[Menomune Taken])) Between Date() And Date()+30)) OR (((DateAdd(&quot;yyyy&quot;,1,[TB Skin Test Taken])) Between Date() And Date()+30)) OR (((DateAdd(&quot;yyyy&quot;,10,[Smallpox Taken])) Between Date() And Date()+30));

 

[fixious]

Any ideas on this. Thanks for your time and patience with me also..