help with how to Dcount two fields in one table

[govnah]

Hi everybody,

I am struggling with finding the write way to achieve a certain task. I hope someone can help me.

Problem:
I have a table
[reservation_tb]
-reserveID
-roomID
-from :[date datatype]
-to :[date datatype]
-status

On Reservation form. I have a button that will check if the reservation_tb table has a room reserved within the date of the new reservation.

Solution Attempt:
I tried to use a DCount function but it seams to be giving me the wrong result.

Dim intRoomID, num As Integer
Dim varFrom, varTo
 
varFrom = Me.from.Value
varTo = Me.to.Value
 
intRoomID = Me.comboRoom.Value
 
num = DCount("[roomID]", "[reservation_tb]", "[roomID] = " & intRoomID & " _
And [from] between " & varFrom & " and " & varTo & "")
 
If num > 0 Then
    MsgBox "Already Exist"
Else
    MsgBox "Reserved Succesfully"
End If
 

when i debug, it looks like the value in the [from] is from the FORM and not from [reservation_tb].

I can't quite figure out how to solve that.
Thank You in advance.

[MikeTheBike]

Hi

Have you tried this

num = DCount("[roomID]", "[reservation_tb]", "[roomID] = " & intRoomID & " _ 
And [from] between #" & Format(varFrom,"mm/dd/yy") & "# and #" & Format(varTo,"mm/dd/yy") & "#")

??


MTB

[govnah]

Thank you soo much for replying. I tried your suggestion but now its giving me "0" as a result instead of "1".


This is me trying to make my problem more clear.


----------------
reservation_tb (current records)
reserveID | roomID | from | to | status
1 | 2 | 26/07/2010 | 30/07/2010 | Reserved
2 | 4 | 26/07/2010 | 28/07/2010 | Reserved

the above is the current records in the table that i want to insert into


----------------
Reservation Form (new)
reserveID | roomID | from | to | status
3 | 2 | 26/07/2010 | 28/07/2010 | Reserved

this insertion should not be possible because the "[roomID]=2" is already reserved on the same day "[from]=26/07/2010"


----------------
Reservation Form (new)
reserveID | roomID | from | to | status
3 | 2 | 28/07/2010 | 28/07/2010 | Reserved

The above update should also not be able to work because [roomID]=2 is already reserved AND "[from]=28/07/2010" falls within the date which "[roomID]=2" is reserved in the "reservation_tb (current records)"... "[from]=26/07/2010" "[to]=30/07/2010"


----------------
Reservation Form (new)
reserveID | roomID | from | to | status
3 | 2 | 1/08/2010 | 3/08/2010 | Reserved

However, the above insertion should be able to work because "[from]=1/08/2010" "[to]=3/08/2010" is deffirent from what already exist in reservation_tb (current records).

[MikeTheBike]

@govnah
Hi

I havn't the time just now to study your code, but I did think before that you had not considered the 'to' date, so maybe this will help

num = DCount("[roomID]", "[reservation_tb]", "[roomID] = " & intRoomID & _
          "And [from] between #" & Format(varFrom, "mm/dd/yy") & "# and #" & Format(varTo, "mm/dd/yy") & "#")
 
    num = num + DCount("[roomID]", "[reservation_tb]", "[roomID] = " & intRoomID & _
          "And [to] between #" & Format(varFrom, "mm/dd/yy") & "# and #" & Format(varTo, "mm/dd/yy") & "#")
 
    If num > 0 Then
        MsgBox "Already Exist"
    Else
        MsgBox "Reserved Succesfully"
    End If

??

This could, of course, be done in one DCount(), but that looked more difficult to understand!

MTB

[govnah]

@MikeTheBike

Thank you so much for taking time out to help me out :)

YOUR CODE SOLVES THE PROBLEM. Thank you again