By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,964 Members | 1,810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,964 IT Pros & Developers. It's quick & easy.

help with how to Dcount two fields in one table

govnah
P: 9
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.

Expand|Select|Wrap|Line Numbers
  1. Dim intRoomID, num As Integer
  2. Dim varFrom, varTo
  3.  
  4. varFrom = Me.from.Value
  5. varTo = Me.to.Value
  6.  
  7. intRoomID = Me.comboRoom.Value
  8.  
  9. num = DCount("[roomID]", "[reservation_tb]", "[roomID] = " & intRoomID & " _
  10. And [from] between " & varFrom & " and " & varTo & "")
  11.  
  12. If num > 0 Then
  13.     MsgBox "Already Exist"
  14. Else
  15.     MsgBox "Reserved Succesfully"
  16. End If
  17.  
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.
Jul 26 '10 #1

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1. num = DCount("[roomID]", "[reservation_tb]", "[roomID] = " & intRoomID & _
  2.           "And [from] between #" & Format(varFrom, "mm/dd/yy") & "# and #" & Format(varTo, "mm/dd/yy") & "#")
  3.  
  4.     num = num + DCount("[roomID]", "[reservation_tb]", "[roomID] = " & intRoomID & _
  5.           "And [to] between #" & Format(varFrom, "mm/dd/yy") & "# and #" & Format(varTo, "mm/dd/yy") & "#")
  6.  
  7.     If num > 0 Then
  8.         MsgBox "Already Exist"
  9.     Else
  10.         MsgBox "Reserved Succesfully"
  11.     End If
??

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

MTB

Share this Question
Share on Google+
4 Replies


Expert 100+
P: 636
Hi

Have you tried this

Expand|Select|Wrap|Line Numbers
  1. num = DCount("[roomID]", "[reservation_tb]", "[roomID] = " & intRoomID & " _ 
  2. And [from] between #" & Format(varFrom,"mm/dd/yy") & "# and #" & Format(varTo,"mm/dd/yy") & "#")
??


MTB
Jul 26 '10 #2

govnah
P: 9
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).
Jul 26 '10 #3

Expert 100+
P: 636
@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
Expand|Select|Wrap|Line Numbers
  1. num = DCount("[roomID]", "[reservation_tb]", "[roomID] = " & intRoomID & _
  2.           "And [from] between #" & Format(varFrom, "mm/dd/yy") & "# and #" & Format(varTo, "mm/dd/yy") & "#")
  3.  
  4.     num = num + DCount("[roomID]", "[reservation_tb]", "[roomID] = " & intRoomID & _
  5.           "And [to] between #" & Format(varFrom, "mm/dd/yy") & "# and #" & Format(varTo, "mm/dd/yy") & "#")
  6.  
  7.     If num > 0 Then
  8.         MsgBox "Already Exist"
  9.     Else
  10.         MsgBox "Reserved Succesfully"
  11.     End If
??

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

MTB
Jul 27 '10 #4

govnah
P: 9
@MikeTheBike

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

YOUR CODE SOLVES THE PROBLEM. Thank you again
Jul 28 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.