rs.FindFirst help, re dates & time values

[lee_cufc]

Hi,
I am stuck with the following part of my system code:

Set rs = Me.Recordset.Clone
[Time Of Appointment] = Dates.TimeOfBooking
[Date Of Appointment] = Dates.DateOfBooking
rs.FindFirst Format([Time Of Appointment], "hh:mm") & Format([Date Of Appointment], "mm\/dd\/yyyy") & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

If rs.NoMatch = True Then
DoCmd.GoToRecord , , acNewRec

Basically, 'Dates.TimeOfBooking' and 'Dates.DateOfBooking' are global variables which hold the 2 values i need. when this form is opened i want it to search for the record which has a match on the 'Date Of Appointment' and 'Time Of Appointment' fields with those values held in the global variables as mentioned above.
So far this code adds new record correctly however returns a sytax error which reads 'missing operator in expression' i am pretty uncertain on how to set the format for searching for date and time values so i guess this is where i am going wrong?
many thanks in advance, any help is most welcome.
Lee

 

[raskew]

Lee -

Would I be accurate in guessing that you didn't write this code.

Please post the complete function, start to finish and show where the Dates.TimeOfBooking and Dates.DateOfBooking are coming from. I've personally never seen global variables expressed in that manner and, conversely, it looks like they are coming from a table Dates.

Bob

 

[lee_cufc]

Bob,

Firstly thanks for the quick reply, i did actually write the code although some is wizard generated

The Global variables are in a seperate module called 'Dates'
I had no experience of using them before and so after some internet research read that this is how they had to be declared? I then read that to access them they must be prefixed with the module name, i tried dates.dateofbooking etc in a different part of my code and it worked great, so i just presumed this was right?

I have stepped through the code as far as i can and those 2 values retrieve the right values so i dont think they are my problem, it is in the rs.findfirst section where my problem lies i think?

 

[Julian]

Why not try something like this:

If DCount("Anyfield", "Mytable", "[Datefield]=" & Dates.DateOfBooking & " and [TimeField]=" & Dates.TimeOfBooking) > 0 Then
DoCmd.GoToRecord , , acNewRec
End If

 

[datAdrenaline]

First off ... you really should not be storing a "Date" field along with a "Time" field ... or variables for that matter. You see, to Access (actually several) applications and databases and Date/Time datatype is a number. In VBA and JET | ACE its equivalent to a Double. The number is the number of days passed a base date. In Access/VBA/JET|ACE that base date is 12/30/1899 12:00 AM, which is numerically equivalent to 0.0. So when you think you are storing just the date component, you are not ... its just that your time component (the right of the decimal) is 0, so you are always storing <somedate> 12:00 AM. The same is true when you think you are storing just the Time component ... you are actually still storing the date component, its just that it is 0, or 12/30/1899 ... so ... you are using twice the amount of resources to store your time of booking. I would suggest just one field and name is TimeOfBooking, and put the full date into that field, then if you need to, you can format the value to DISPLAY either the time component or the date component.

Next it seems as though your code is doing some navigation. You do have a few problems with it. First and formost is, the construction of your rst.FindFirst code line. The argument of the .FindFirst method should look like an comparison expression ... so you are right in saying that your problem is there. Also, you are using .EOF to see if your .FindFirst is successful or not. That is no a valid test to see if .FindFirst found a match... you MUST use .NoMatch, which you do later on ... but not really in the best place ... also, what purpose do the controls serve?

[SIZE=1]With Me.RecordsetClone[/SIZE]
[SIZE=1]   If Not (.EOF And .BOF) Then[/SIZE]
[SIZE=1]       rs.FindFirst "DateValue([YourDateField]) = #" & Format(Dates.DateOfBooking, "yyyy-mm-dd") & "#" & _[/SIZE]
[SIZE=1]                " And TimeValue([YourTimeField]) = #" & TimeValue(Dates.TimeOfBooking) & "#"[/SIZE]
[SIZE=1]       If .NoMatch Then [/SIZE]
[SIZE=1]           DoCmd.GoToRecord , , acNewRec[/SIZE]
[SIZE=1]       Else[/SIZE]
[SIZE=1]           Me.Bookmark = .Bookmark[/SIZE]
[SIZE=1]       End If[/SIZE]
[SIZE=1]   End If[/SIZE]
[SIZE=1]End With[/SIZE]

Hopefully that helps you out ... but do take note, this is has and AIR CODE disclaimer! ...

 

[lee_cufc]

Thanks For Your Help Guys,
I have now got that to work with some slight tweaking and playing around etc, i have since realised that i need to add a 3rd condition for the search which is a field 'Stylist ID'

this is the modified code with the line i'm now having problems with in blue:

Set rs = Me.RecordsetClone
If Not (rs.EOF And rs.BOF) Then
rs.FindFirst "([Date Of Appointment]) = #" & Format(Dates.DateOfBooking, "yyyy-mm-dd") & "#" & _
" And ([Time Of Appointment]) = #" & (Dates.TimeOfBooking) & "#" & _
" And ([Stylist ID])= " & (Dates.StylistID) = ""
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec

I realise that the way i've done this is probably not the best etc but it works as a quick fix which all i currently need, with this in mind can any of you guys tell me the correct syntax for the line above in blue? as this is incorrectly returning no matches when i run it currently.

p.s i had the first 2 lines of the rs.findfirst code working before attempting to add the 'Stylist ID' line so i know it is definately that line (in blue) thats incorrect.

Many thanks
Lee

 

[lee_cufc]

Please can anyone help me?
Im so close to achieving what i need apart from this little bit!

any help would be awesome
Lee

 

[datAdrenaline]

Hello! ... Congrats on your progress! ...

...Hmmmm .... Look real closely here ...
And ([Stylist ID])= " & (Dates.StylistID) = ""

The string that will be return from that is ...

And ([Stylist ID])= True
..Or ..
And ([Stylist ID])= False

This is happening because you are concatenating the boolean returned by the equality comparison of Dates.StylistID and a ZLS ("").... Now, if that is what you wanted then great! ... but most likely it was not based on your post ...

-------

... What is the datatype of [Stylist ID]? ... Number?, Text?, Date/Time?

... if it is a Number, no special dilimiters needed
And ([Stylist ID])= " & (Dates.StylistID)

... if it is Text, you need single quotes to delimit
And ([Stylist ID])= '" & (Dates.StylistID) & "'"

Or double quotes (which must be doubled if used between the dbl-quotes of a literal string)
And ([Stylist ID])= """ & (Dates.StylistID) & """"

... if it is a Date/Time, you need an octothorpse to delimit
And ([Stylist ID])= #" & (Dates.StylistID) & "#"

Let us know if you have questions! ...

 

[lee_cufc]

The value of the field was a number, so i used the code you suggested and it worked first time!!

I cannot thank you enough, the generosity of guys like you on here to take time to help others really amazes me!

Thanks so much again,

Since your advice worked so well i wonder if you could have a look at another piece of code that isnt working for me please?

[Forms]![NEW]![(Dates.Colour)].[BackColor] = [vbRed]

In this code i'm wanting it to go to the form named 'NEW' and change the back color of a textbox to red.
The name of the text box is stored in the global variable 'colour', which is declared in the module 'dates' (hence the dates.colour part)
On stepping through my code i know that the name of the textbox is being stored correctly in the variable im just unsure of how to use this code with a variable in?

Any thoughts or ideas?
thanks again
Lee