Modules & VBA :: Date Expression For DCount And Where Open Arguments
Oct 9, 2014
I am getting something wrong but I don't know what - date related.
Take this code for example:
Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=" & varDate) > 0 Then
MsgBox "Go Away", vbOKOnly
Else
Go on and do the real work
End If
End Sub
The DCount line is being completely ignored, no error message, it just cracks on as though there were no records found (but they are definitely there!) I have tried:
Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate='" & varDate & "'") > 0 Then
MsgBox "Go Away", vbOKOnly
But that throws up a Type Mismatch (which is what I would expect). Then I tried
Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=#" & varDate & "#") > 0 Then
MsgBox "Go Away", vbOKOnly
And it gets ignored again.
I am having similar trouble trying to open another form using the same field as the open argument, and getting similar results, so it is clearly me that is wrong.
I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax. DCount("*","obsvnofilterqry","(Date_Closed)=MONTH( Date())").Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky.
Hi. I'm putting together a database of property clients and i have almost finnished but as always i seem to get so very close to the end of a project and i hit a wall.
background:- I have about 5 different tables which mostly have relationships but there is one main table (clientDetails) which holds the main record this has a very predictable clientID primary Key. the other tables have equally predictable keys for the tables ie: LettingID for a letting client insuranceID for insurance clients etc.. the deal is really that EVERYONE is a client so they get the main details filled out and another record in another table using the primary key of the main table in all other tables. one client can have many other records relating to them useing the primary key.
The Problem:- I have made lots of nice forms that all link up together so you can edit and brose all of the records in the database, and this works very well.. however my problem comes when i want to add a new record in one table that relates to the same record in another using the forms not the table view. eg. on form 1 (clientDetails) i add a new client to the database all fine even using the autonum for the PK. now i bring up the record of that client and i have a button (addProperty) this brings up another form (propertyDetails) using the PK from the previous. this works fine if there is already an entry in the DB but if not then the PK is autonumbered..
What i need:- I need to be able to open up a form using PK then (addProperty) button to the next form which finds that record using the pk (clientID) and FK (propertyID) and if there is no FK relating to the PK then to autonumber and use that... so i basically transfer the PK from form 1 to form 2 regarless of any other data relating to it, and auto numbering to the next number in that table for the FK.
eg.
form1 form 2 ------- > --------- PK=100 PK=100 FK=5
this is probibly really basic.. but i hope someone can help ..
Is there a command line argument to open AND close a db? I am running a schedule task to open a db which has an autoexec macro but I don't want the db to stay open after running the macro. Or is there a close db function in a macro? I see close form but not db.
Im a relative novice with access VBA, and I'm really struggling with using Dcount with date variable. All I want to do is count if a certain date appears in a table. Here is the extract from my code:
Whatever dates are in tblworkoutlogs, datecount is still = 0...I've trawled the net and tried many variations of the code but no success!if I change all the date formats to strings in the code and the tables, it works so I know i'm looking in the correct place.
I'm trying reference a dcount on two fields, one is a string and works fine, the other is on a date which does not.
I've investigated the problem and found a couple of references to making sure I use a # symbol to reference the date but I think the thing that's throwing the comparison out is that the date field stores time as well, even if you can't see it?
I thought should count the records in the healthcheck table that match the check_date field against the first_health field on the current form, but I keep getting a 0 count even though the dates seem to match.
I can't get the following DCOUNT working for the life of me: DCount("EmployeeName", "tblAttendance", "EmployeeName = '" & cboEmployee Name. Value & "'" And "AttendanceDate = #" & txtAttendanceDate.Value & "#")I have checked this forum along with Google and there seems to be no easy way to do this? I have tried editing the syntax over and over again. I have been able to run a query and get the desired result in that query; however I am unable to call that result in VBA.
I have a python script "runAll" that takes two arguments: processID (the primary key of one table as a string) and a filename (a full file name path as a string).
Is it possible to run a python method with arguments from inside a VBA script (activated by a button press)?
My python script is called XMLGenerator.py, and the method I want to run is called runAll(processID,filename).
I want to count the number of occurences in a datatable when loading a form. BUT i want only to count if it is equal to a value on a text box on this same form.
what i made:
1-created a unbound textbox 2-inserted on "control Source" this expression =DCount("*";"T_Pendentes";"[disponente.Procurement]= " & [me].[disponenteProcurement].[Value] & "")
where * is to count all, T_Pendentes is the table, and de expression is the criteria.
I have a after update event that will match the written record with any exist record in a table in the field "OrgName". If it doesn't find exact match, will call a function with a "soundex" algorithm to see if there is only a misspelling or another name altogether.
In the afterupdate event, I have a string called strOrg (wich is the name I want to compare).
I have the soundex function in a module, so I can use it for several form generally.
What I want is to pass the strOrg to the soundex function, however I don't know how to declare the variables. however I keep having this error:
"compile error: Argument not optional"
and goes to the line tagged as 1 in the private sub afterupdate
The afterupdate sub is the following:
Code:
Private Sub tOrgName_AfterUpdate() Dim strOrg As String strOrg = Me.tOrgName.value If IsNull(DLookup("orgID", "torg", "OrgName = '" & strOrg & "'")) Then resMsg = MsgBox("This organization name is not in the list. If you want to look for similar names press YES, if you want to register a new organization press NO.", vbYesNoCancel, "Organization not found")
[Code] ....
and the soundex function is declared as
Code: Public Function Soundex(strOrg As String) As String Dim Result As String, c As String * 1 Dim Location As Integer
I am trying to check for when a user trys to enter a duplicate number.
The control that I am checking is in a subform on the main form:
Main: frmCandidateInfo Sub: frmTestInfo
Control on the subform is: RankOrder
I am trying to check the control entry against the table entry:
Table: tblTestEvents Field: RankOrder
Here is what I have:
Code: Private Sub RankOrder_BeforeUpdate(Cancel As Integer) Dim lngRankDup As Long lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!frmCandidateInfo!sfTestInfo!Form!RankOrder, 0)) If lngRankDup <> 0 Then MsgBox TestEventID & " already exists in the database" End If End Sub
I know that the error is due to the argument not being correct, but I am not sure how to fix it.
Summary: Using DCount in MS Access 97 to output the name of an event and the total number of occurances per a user input date range.
e.g. the table is called EventData and it is a table of the types of car repair performed, and the fields are:
EventType Date Desciption other Time ======== ==== ============ ==== fix flat 17/01/06 comment.... 101pm brakes 15/01/06 comment... 504pm tuneup 02/01/06 comment... 725am paint fender 03/01/06 comment.. 852am
My issue is to use a subquery for intermediate results and then another query.
I would like the user to enter the start and end dates, and then for output to be:
Output:
EventType Num Occurances ======== ============ fix flat 2 brakes 5
approach 1, use subquery called SummaryQuery:
SELECT EventData.EventType, EventData.Date FROM EventData WHERE (((EventData.Date) Between [Type the Start date:] And [Type the End Date:]));
This gives me output of all the events types in the date range and the time.
Then if I query this via: SELECT SummaryQuery.EventType, DCount("*","SummaryQuery") AS TotalCount FROM SummaryQuery GROUP BY SummaryQuery.EventType HAVING (((EventData.EventType) Between [Type the Start date:] And [Type the End Date:]));
Here I am using DCount to get the num of ocurrances.. but it is not working. I get the error that "Access cannot find the name "type the start date" "
approach 2, Use only one query:
SELECT EventData.EventType, DCount("*","EventData") AS TotalCount FROM EventData GROUP BY EventData.EventType HAVING (((EventData.EventType) Between [Type the Start date:] And [Type the End Date:]));
for this, I get no output, but the query runs.
Any comments or help with this is appreciated!!!!!!!!!!!!!!!
Hi, The query statement below is meant to extract the number of References with a type code of 'M' on the table tblHDRef, then present it under a column named 'Modified':
I'm having abit of a problem with the DCount function where date fields are involved.
Basically, I have a table - TblMain. Within that table there is a field -DateRegist. The data type of this field is set to Date / Time - Format short date.
Elsewhere I have set up a form with a text box. The idea being this text box will count the number of 'DateRegist' fields where a date has been entered between two set dates. (There will be four text boxes in total, one for each quarter of the year)
When I use Date() etc in the control their are no problems. However, when I try and use an actual date it all goes pear. I figure it has something to do with it being a date field bit not sure exactly what.
This is what I have so far (don't laugh, I know its miles off the mark but I am trying)
I know I can do four separate queries, each pulling off a quarters data and then draw off the Dcount from each of them but after two days of trying to figure it out I would like to know where I'm currently going wrong - (If you consider doing to four queries will be quicker for the abstraction of the data then let me know but I would still like to know what I'm doing wrong for future reference)
With a form with two fields one is a date field formatted as short date and the other is a lookup combo box that I use to lookup predefined times and is formatted as short time. The date and time are stored in the same table as date/time in separate fields. I attempted to use Dcount, and get syntax error missing operator. Below is one of a hundred that i have tried after scouring this forum and the net. I ended up converting the Appt_Time field to a text field in the table to eliminate one date field but still got no where.
Code: Private Sub Appt_Time_BeforeUpdate(Cancel As Integer) 'Check table for duplicate If DCount("[Appt_Time]", "tblexams", "Appt_Time= & Appt_Time & And [Sch_Date] = #" & Sch_Date & "#") > 0 Then 'Message box warning of duplication
I would like to have a text box display the number of records for a selected title that fall within a selected date range. Been looking around for a while and have this so far;
=DCount("Discussion_Title","Discussions","[Discussion_Title]='" & [cboType] & "' And [Discussion_Date] = Between ([txtStartDate] And [txtEndDate])'")
I get an #Error message. I just threw that together because it describes what I want, but I know there are syntax problems.
I need to check the combination of a text box and a combo box that are bound to the same table for duplicates and then give a message box telling of the existing records. The 2 fields are set up as a compound index so access displays a generic message but I am trying to customize the message without doing it through error trapping (because I want it earlier in the processes on the form).
I am trying to us Dcount as follows but this is not working. It seems to be checking for either rather than a combination of the 2 fields because I get my message if either the Description OR the Group is duplicated (both of the text box and the combo box can have duplicates and it is only the combination that is singular) .
If DCount("[Description]", "[ItemTBL]", "[Description] = '" & Description & "'") And DCount("[Group]", "[ItemTBL]", "[Group] = '" & Group & "'") > 0 Then MsgBox "This Item is already in the database.", vbExclamation, "Already in Database"
Upgraded from Access 2003 to Access 2010. My DCount query was working fine in Access 2003 for many years but now I am having issues. Everything is written in VBA.
Routine: 1.) Insert records into table tblEmployee 2.) Query table tblEmployee to count how many employees are in each department using DCount.
Issue: DCount returns 0 for all Departments.
Findings: If I manually open table tblEmployee and edit an employees department (I actually named it the same department), DCount will then find 1 person in that department (the record I manually edited). It seems a manually edited record will be counted but anything written by the insert query is ignored.
I have a tblManualRewardIncident in there I have 3 fields I want to have my staff in put these rewards via a frmRewardIncidentEntry However im concerned over another staff member in putting the same data so I want the form to highlight on update that a duplicate is there already so cancel the data entry.
The 3 fields that need to check and match are StrataID , DateOfIncident , EndDateOfIncident these are all in the tblManualRewardIncident and appear as buttons in the form frmRewardIncidentEntry the buttons are as follows cboStrataID , txtDateOfIncident , txtEndDateOfIncident
I want the VBA to run when I CLICK another button on same form cboSQCDP..All 3 fields must match exactly to flag a issue.
StrataID , DateOfIncident , EndDateOfIncident from tblManualRewardIncident
If it doesn't find a match continue and add the inputs in these boxes to the table if it finds a match flag a message and delete the data from the form.
In my database when an item is currently being loaned the item's loaned field will have a 1 to indicate this. When the item is returned the value is 0. Only so many items are available and when I use the DCount function it is counting the 0s as 1s.
My DCount statement is not finding a record that I know is there.
Code: Private Sub Transfer_WN_Books_Click() Dim ST As String ST = "H" If DCount("*", "Books", Author = " & Me!AuthorID & " And StatFlag = " & ST & ") > 0 Then DoCmd.Beep End If End Sub
I have a database (I've attached it for you). I'd like to set it up so that in the "incentive scheme" form when the field [term 1 allow] is changed and is more than 0 then have the form "permissible explanation" appear with that record so the user can add an explanation, they should not be allowed to leave it empty if the [term 1 allow] is still >0 and then save and return to the "incentive scheme" form for further input.
I'm trying to use DCOUNT to check to see if a date exists and if it does then i can cancel the input. The code im using is as follows however its returning datatype mismatch.
Code: If DCount("[pick_date]", "tblPicker_Stats", "[pick_date] = '" & txtPickDate & "'") > 0 Then MsgBox "Statistics for this date already exist", vbOKOnly, "Error" End End If
