16. juli 2014 - 12:35Der er
18 kommentarer og 1 løsning
Probleme med Dcount
Hej igen eksperten....
Jeg har et problem med Dcount, hvor jeg har noget kode der skal checke om et lokale kan bookes på i et valgt tidsrum.
Hvis den finder en record der opfylder de indtastede kriterier, så er lokalet booket indenfor tidsrummet, og kan derfor ikke bookes igen.
Der er flere dcounts, som skal lede på forskellige kriterier, for at imødekomme at man laver forskellige kombinationer af start og slut tid for bookingen. Jeg er ikke færdig med alle de forskellige "Where" kriterier endnu.
Problemet er at selvom jeg bruger kriterier der burde udløse en record, så sker det ikke. Jeg har checket ved at bruge kriterierne i en forespørgsel, og den rigtige record kommer op. Kan nogen af jer se hvad der er forkert i min Dcount ?
Her er koden:
Dim frakl As Date, tilkl As Date, dato As Date
frakl = [Forms]![FM_book_lokale]![tidfra] tilkl = [Forms]![FM_book_lokale]![tidtil] dato = [Forms]![FM_book_lokale]![dato]
where1 = "[DT_rum].[IDrum]= " & [Forms]![FM_book_lokale]![IDrum] & " AND [DT_tilbudskalender].[dato] = #" & dato & "# AND [DT_tilbudskalender].[starttid] <= #" & frakl & "# AND [DT_tilbudskalender].[sluttid] >= #" & tilkl & "#" where2 = "[DT_rum].[IDrum]= " & [Forms]![FM_book_lokale]![IDrum] & " AND [DT_tilbudskalender].[dato] = #" & dato & "# AND [DT_tilbudskalender].[starttid] > #" & frakl & "# AND [DT_tilbudskalender].[sluttid] < #" & tilkl & "#"
If DCount("*", "F_rum_paa_dato_og_tid", where1) > 0 Or DCount("*", "F_rum_paa_dato_og_tid", where2) > 0 Then P = MsgBox("Rummet er booket i det angivne tidsrum", vbOKOnly) Exit Sub Else 'her skal der være en update forespørgsel som opretter bookingen med IDrum, dato, tidfra, tidtil, tilbudstype(mødebooking) og IDmedarbejder P = MsgBox("rummet kan bookes i det angivne tidsrum", vbOKOnly) End If
"Try making a query and include the where clause you show above
What is the result? "
I did that, and it gave me the exact record as it was supposed to :)
I tried formatting the dates to mm/dd/yyyy, but the first date it didnt format, but the second it did... That was probably the most strange thing ive experienced in VBA ever, cause it was 2 unformatted textboxes in a form, that i took the values from in the same way, and put them into 2 variables that was DATE types... really weird.
Im gonna try the datevalue thing you wrote right now :)
FROM DT_rum INNER JOIN DT_tilbudskalender ON DT_rum.IDrum = DT_tilbudskalender.IDrum
WHERE (((DT_rum.IDrum)=1) AND ((DT_tilbudskalender.dato)=DateValue(#7/14/2014#)) AND ((DT_tilbudskalender.starttid)<=TimeValue(#12/30/1899 10:0:0#)) AND ((DT_tilbudskalender.sluttid)>=TimeValue(#12/30/1899 11:0:0#)));
You are selecting on two tables with INNER JOIN, but in the Dlookup you select from F_rum_paa_dato_og_tid which I guess is a query.
So you should try making anew query like this
SELECT count(*) FROM F_rum_paa_dato_og_tid WHERE (((DT_rum.IDrum)=1) AND ((DT_tilbudskalender.dato)=DateValue(#7/14/2014#)) AND ((DT_tilbudskalender.starttid)<=TimeValue(#12/30/1899 10:0:0#)) AND ((DT_tilbudskalender.sluttid)>=TimeValue(#12/30/1899 11:0:0#)));
the query at #9 where ive tried the kriterias IS the F_rum_paa_dato_og_tid query...
I cant send the DB, unfortunately, it contains sensitive data, but thankyou for the offer !
im giving up for today..
basically im asking the DB
if there is a record where an entered starttime is between or equal to a starttime AND endtime in the db
if there is a record where an entered endtime is between or equal to a startime AND endtime in the db
if there is a record where and entered starttime is before or equal to a starttime in the db AND and entered endtime is equal to or after an endtime in the DB.
when i ask that in the query with entered criterias, it gives me the right result, but its like it doesnt work with Dcount....
I have to quit for today... my head is turning the wrong way.
TY Terry for your effort, ill be giving it a try again tomorrow.
No problem Kim, we all make mistakes at some stage its part of the learning process.
Here comes an answer, have a good holiday.
BR Terry
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.