Search results

ok, it turns out that if I join tables in a query before carrying out the function, it errors out. If I do a make table query, and then make a query only pulling from the table, then the function works. Any idea why? Is it possible the make my code create a table to do this? Thanks, Tyler

I would post it, but unfortunately it is 50 mb.

Sorry, I totally screwed up. since both functions were calculating, I forgot to put the breakpoint in the other one. when I put a breakpoint on the line If Not rst.EOF And Not rst.BOF Then it has the error. sorry, Tyler

The DAO library is checked, and it doesn't work. The odd thing is that it is on the same computer. The only difference is the database. Could it be because of how I transferred the module(draging and dropping from one db to another)? Thanks, Tyler

Ok, I did the tried the breakpoint and it seems to error out when I do the currentDB.(just after the strsql assignment) Set dbs = CurrentDb

I just tried to change the names of the doubled variables and unfortunately that did not work. Thanks for the suggestion, Tyler

I tried to paste the SQL into a query and it works fine, so I guess that is not the issue. As far as using dlookup, I had tried. My problem was that I am looking for the start date and end date of a continuous length of stay. In otherwords, I want the function to return the min and max where...

So I finally got this function working with the help of another member, then I transferred it to another database to do the same exact thing and I get error 3061 too few parameters expected 1. I have checked and double checked the table and field names to no avail, but still no luck. If...

Never mind all together :). I finally figured out what you did and was able to adapt it. Sorry for being such a pain ;). Thank you so much for all of your help and time, Tyler Here is my final code. Public Function patientDischarge(patientName As String, endDate As Date) As Date...

Actually, I figured out the answers to two of the three questions, so you can disreguard them. Now, the only one I have is how to adapt this function to work for the end date? Thanks, Tyler

Sorry, one more question. In this part of the code: i = 0 'initialise counter While Not rst.EOF And Not rst.BOF If rst.RecordCount = 1 Then 'if theres only one entry no furter testing patientDischarge = rst!endDate Else Startdates(i) =...

Sorry to ask again, but I can not seem to figure out how to make the end dates work like the start dates do. This code works for most of them, but it seems to have an issue with some of them. For example: I receive the following for one of the patients(picture attached). The first record is...

If you don't mind me asking, what does the line in the SQL string after the and do? Is that how you are determining the record the patient name is currently passing to the function? Thank you again, Tyler

That is amazing! You are awesome. This is exactly what I was looking for. Now I just have to create a function to return the end date. I will try to follow your code to create that. Thank you so much:D, Tyler

Yes and no. I do want to total the visits, but only for each length of stay. I also need to have it return the dates of each beginning and ending length of stay, not just the length/number of days. Therefore, if there is a break in the dates for a specific person, then I need the function to...

I called the function in a separate query from where the function is pulling its record set. I was able to fix the comma issue by inserting quotes on either side, but it only returns the last value as you stated. I will attach an example database for you to look at if you wouldn't mind...

There is an error because the Patient Name has a comma in it, so it is throwing off the syntax. EX: smith,j Do you have any idea how I could fix that? Also, will this give the end date of each length of stay, or just the max end date for each patient name? Thanks, Tyler

I tried to put in patient1 as string, but it froze. I don't know whether this is because it has to go through 1700 records or if it is stuck in a loop? I am just looking for a faster way to do this process other than exporting the data to excel and entering all of the dates manually. I...

Would the dmin and dmax functions give me the max and min dates for a length of stay? If so, that would be awesome! How would I go about using that function? Thanks, Tyler

Ok, now it definitely works. The only problem I am having now is that it only returns 12:00:00 AM in every field. Does anyone know why this would be the case? Thanks, tyler Public Function dateStart() As Date 'Create My variables Dim db As Database Dim rstOriginal As Recordset...