I know it's in the American format and it needs to stay that way regardless of which country you are in. This is what the database engine uses. Like I mentioned, I know why the format() was applied the way it was.
Let's see your current code.
VBA/SQL Logic problem editing a record in a form
- Thread starter cyd44
- Start date
Let's see your current code.
Ok I have attached the current Db which is small. I had to change the Date format back to dd/mm/yyyy because it complianed of a data mismatch agian when I changed it back to mm/dd/yyyy? You will notice that I have changed the time format to Hh:Nn:Ss AM/PM also as I found this Syntax on the web.
Anyway the code does not give any compile error and runs, but it still does not validate correctly. I only have 4 records, please choose one to edit and conflict the times and yoy will see it still believes it to be Valid.
I would appreciate you looking at this small Db for me. many thanks
Anyway the code does not give any compile error and runs, but it still does not validate correctly. I only have 4 records, please choose one to edit and conflict the times and yoy will see it still believes it to be Valid.
I would appreciate you looking at this small Db for me. many thanks
The database is already in Access 2007 File Format, the only other options I have are 2000 or 2002/2003. I do not have any option for 2007 and when I look at the Option pane it tells me that the Default is set to 2007?
Happy to save as any of the others if yoy wish
Happy to save as any of the others if yoy wish
Heres another frustration, although the Save & Publish function Gives the alternatives IE 2000 2003 etc, it will not let you save in anything other than the deefault? Obviously, 2010 is not backwards compatable which is great isnt it?
You are using features that were introduced in 2010 so obviously that isn't backwards compatible. Here's a list:
http://msdn.microsoft.com/en-us/office/cc907897
And here's how you could save it in Access 2007:
http://office.microsoft.com/en-us/a...se-in-an-earlier-file-format-HA010341553.aspx
All I need is the related tables (with some test data), related queries and the form. So you can try to copy and past those from the 2010 db to a new 2007 db shell.
http://msdn.microsoft.com/en-us/office/cc907897
And here's how you could save it in Access 2007:
http://office.microsoft.com/en-us/a...se-in-an-earlier-file-format-HA010341553.aspx
All I need is the related tables (with some test data), related queries and the form. So you can try to copy and past those from the 2010 db to a new 2007 db shell.
I can open it now 
Your main problem was you were using dteEndTime in the StartTime field and dteStartTime in the EndTime field. But I have added some extra bits and taken some things off so have a look at the attached.
Your main problem was you were using dteEndTime in the StartTime field and dteStartTime in the EndTime field. But I have added some extra bits and taken some things off so have a look at the attached.
Hi vbaINet,
Thanks for this, I can see what you have done but you have made the query to be invalid only when BookTime = dteStartTime and BookEndTime =dteEndTime.
This does work if I try to edit for the same times but what I am trying to validate is:-
If the Booking Starts Between another Start and End Time or the Booking Ends between another Start and End Time.
Initial thought was therefore
If we test each booking to see if BookTime was greater than dteEndTime AND BookEndTime was less than dteStartTime. If the DCount sees a booking which does not match this criteria then it should increment. Is this logic not sound?
Thanks for this, I can see what you have done but you have made the query to be invalid only when BookTime = dteStartTime and BookEndTime =dteEndTime.
This does work if I try to edit for the same times but what I am trying to validate is:-
If the Booking Starts Between another Start and End Time or the Booking Ends between another Start and End Time.
Initial thought was therefore
If we test each booking to see if BookTime was greater than dteEndTime AND BookEndTime was less than dteStartTime. If the DCount sees a booking which does not match this criteria then it should increment. Is this logic not sound?
That is correct.
I have been playing with your form and have nearly got it to work by taking out the MyID check and putting the check to BookTime > dteEndTime And BookEndTime < dteStartTime. It works if I change start time great than any record Start time but if there is no record with a start time earlier than the change then it treats it as Valid even if end time conflicts.
I feel we are nearly there but it is either some logic error or an application problem
I have been playing with your form and have nearly got it to work by taking out the MyID check and putting the check to BookTime > dteEndTime And BookEndTime < dteStartTime. It works if I change start time great than any record Start time but if there is no record with a start time earlier than the change then it treats it as Valid even if end time conflicts.
I feel we are nearly there but it is either some logic error or an application problem
To Begin with
Change Record 3 to Start at 11AM and end at 3pm
Change record 4 to Start at 3pm and end at 4pm
If you now edit record 4 to Start at 9am it will show valid
If your change record 4 to to Start at 1pm it will show Invalid.
This is assuming of course that we are using Start > End AND End < Start, not your last version
Change Record 3 to Start at 11AM and end at 3pm
Change record 4 to Start at 3pm and end at 4pm
If you now edit record 4 to Start at 9am it will show valid
If your change record 4 to to Start at 1pm it will show Invalid.
This is assuming of course that we are using Start > End AND End < Start, not your last version
Hi vbaInet,
What a star you are, I can see that the logic was quite complex and almost understand what you have done. I did realisie that we were missing a OR statement somewhere but could not work it out myself. Could you please explain to me why we have to use the -1 on 3 of the times and only 1 one one of the times.
I have tried numerous changes on the form and all have worked. This procedure works much better and I am extremely grateful for your help.
going back to the orginal code (want to try and understand) why did it give random results as this was wierd? It was definitely the time fields causing the problem I feel as it worked only some of the time?
It would be useful to try and understand the problem with the original logic and understand the problems using Time conditions for the future.
Once agian my friend, Many Many thanks.
Regards
What a star you are, I can see that the logic was quite complex and almost understand what you have done. I did realisie that we were missing a OR statement somewhere but could not work it out myself. Could you please explain to me why we have to use the -1 on 3 of the times and only 1 one one of the times.
I have tried numerous changes on the form and all have worked. This procedure works much better and I am extremely grateful for your help.
going back to the orginal code (want to try and understand) why did it give random results as this was wierd? It was definitely the time fields causing the problem I feel as it worked only some of the time?
It would be useful to try and understand the problem with the original logic and understand the problems using Time conditions for the future.
Once agian my friend, Many Many thanks.
Regards
Oops, amend this line:
Code:
"([BookTime_] BETWEEN '" & Format(DateAdd("n", [B][COLOR=Red]1[/COLOR][/B], dteStartTime), "Medium Time") & "' AND " & _
Code:
[COLOR=Red]([/COLOR][COLOR=Blue]([/COLOR]Booktime > dteStartTime AND Booktime < dteStartTime[COLOR=Blue])[/COLOR]
OR
[COLOR=Blue]([/COLOR]BookEndtime > dteEndTime AND Booktime < dteEndTime[COLOR=Blue])[/COLOR][COLOR=Red])[/COLOR]As previously explained, you were using dteENDtime in the BookTIME criteria and dteSTARTtime in the BookENDtime criteria. It was mixed up. Besides that the logic just wasn't enough to cover all possibilities.
Hi there,
Sorry but I need your help on differences between your version of access and mine (2010). I have put your code into my system and have the following problems:-
1. Had compile error on the declaration of [BookTime_] and [BokEndTime _] , took out the _ and it liked that.
2. My version does not like me putting the field condition in the query. Left this out and it appearec ok.
3. Ran the sub and am receiving 3464 error Data Type Mismatch on the DCount variable (this had been set back to the table rather than the query by the way)
I suspect that my problems are simply to do with the different versions but as 2010 is the only one I have ever worked with I am unsure.
I would really like to use this code and dont want to fiiddle with it until I consult with you first.
Sorry but I need your help on differences between your version of access and mine (2010). I have put your code into my system and have the following problems:-
1. Had compile error on the declaration of [BookTime_] and [BokEndTime _] , took out the _ and it liked that.
2. My version does not like me putting the field condition in the query. Left this out and it appearec ok.
3. Ran the sub and am receiving 3464 error Data Type Mismatch on the DCount variable (this had been set back to the table rather than the query by the way)
I suspect that my problems are simply to do with the different versions but as 2010 is the only one I have ever worked with I am unsure.
I would really like to use this code and dont want to fiiddle with it until I consult with you first.
Yes I noticed that but 2010 will not accept the query. In the property panel for the time and date fields it does allow you to set the format underneath control source and these have been set to medium time. If I try to put a condition in the field name on the query pane it does not accept it? Would this be a difference between 2007 & 2010?
I have Debug set and what I have noticed is that the form vaiables shown for the Time are 09:00:00 and if I look at my Table it is shown as 09:00 AM. Would this be the cause? It is definitely a Time calculation problem I feel and I did read somewhere about a problem with time and the seconds element causing problems. Unfortunitely, I cant remember where I read this?
Sorry for continuing this but I dont want to play around with this code without bouncing thoughts off you first.
I have Debug set and what I have noticed is that the form vaiables shown for the Time are 09:00:00 and if I look at my Table it is shown as 09:00 AM. Would this be the cause? It is definitely a Time calculation problem I feel and I did read somewhere about a problem with time and the seconds element causing problems. Unfortunitely, I cant remember where I read this?
Sorry for continuing this but I dont want to play around with this code without bouncing thoughts off you first.
Similar threads
- Question
