In A Form, Some Dates Work, Some Don't

Jun 27, 2006

I've got a form with several date fields (in Medium date format), with each field bound to fields in a table, where also the dates are in medium format.

I also have the form set up where I can enter dates manually, or I can double-click and pick dates from a calendar form, or I can click on an icon and pick dates from the calendar form. Each date goes through the same bunch of processes (i.e., queries and code).

My problem: Some dates work, and some dates don't. On some date fields, I can key in a date, exit the form, re-open the form, and my dates show up (since they were processed into my table.) However, some other date fields do not get processed! I enter dates into these fields, exit the form, re-open the form, and there are no dates!!

I have checked all fields in my table..... they are all date fields, of medium date format. Same for my form.

Why would some dates work just fine while others don't work? If anyone has some suggestions I could try, I would be grateful. What might I look at in my application?

View Replies


ADVERTISEMENT

SQL Statement Dosent Work With Dates

Mar 3, 2008

Why doesent this work
aa = Me.Due
aa = DateAdd("m", aa, Date)

Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < " & aa & " ORDER BY [Tasks].Staff_Name;"



the sql dosent display any results!!!

View 14 Replies View Related

Calculating Number Of Work Dates From 2 Date Fields

May 29, 2005

In my Query I have a Date Received field and a Date Approved field.

I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.

It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.

How would I go about doing this? I would need to add a new column to my query?

What would I put for the field and criteria? What kind of expression do I use?


Any help will be appreciated, thanks

View 14 Replies View Related

Count Work Days - Calculate Difference Between Two Dates

Jun 8, 2015

How can I calculate the difference between two dates but I only want to count the work days? So if was today and I wanted to go until 6/15/2015 the difference would be 5 and not 7 because I do not want to count Saturday or Sunday. Is there a special %datediff function where I would only count work days?

View 4 Replies View Related

Tables :: Database With Updating Dates - Track Current Projects For Team At Work

Dec 27, 2013

I am creating a database that tracks current projects for my team at work.

Some projects are only due once (e.g., mailed brochures due on 1/1/14) and some are due at scheduled intervals (e.g., status report due monthly, quarterly, etc.)

Ultimately, I'm hoping that my end result will allow us to click on a form and look at what everybody has due that day, in the next 7 days, and so forth.

View 3 Replies View Related

Making A Look-Up Form Work The Way I Want It To Work!

Feb 27, 2006

We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.

To do this, I have created two tables: tblDRIVER and tblLICENSE.

The fields in tblDRIVER are:
pkfDriverIndex
strLastName
strFirstName
strInitial
strAddress1
strAddress2
strCity
strState
strZIP
datBirthDate

The fields in tblLICENSE are:
pkfUpdateIndex
fkfDriverIndex
datDateUpdated
strState
strLicNumber
datExpirationDate
ynViolations
ynActive

Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.

My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).

All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.

Thanks for the help.

View 9 Replies View Related

Queries :: Counting Days Between Dates - Vacation Period And Work Days

Sep 11, 2014

I have a form called subfrm_vactions

rowsource is a query called qryVacations

the query should calculated two things

1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)

2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)

the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want

simply i want to calculate the work days.

View 5 Replies View Related

Search Code Work On One Form But Not The Other Form?

Jul 18, 2015

* This code works to search frmMember for criteria found in tblMember:

Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

[Code] ....

* But this next code which I have copied and Pasted to frmEventInput, then renamed the frm and tbl, retrieves the same criteria as the above (tblMember) instead of the tblEventInfo...

Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

[code]...

What am I missing?

View 3 Replies View Related

Query Won't Work In A Form

Aug 1, 2007

hi

i have a DB which tracks order numbers,
the main table is a SQL table linked in, and there are lots of local tables as well, one of which is called userDetails and contains a field useremail

i want to open a form and only show records from trackermaindata that contain my useremail -


SELECT dbo_TrackerMainData.userEmail
FROM dbo_TrackerMainData, userDetails
WHERE dbo_TrackerMainData.userEmail=userDetails.useremai l;

when i double click this query in access it works and it shows me only my records, but when i try and run this query from a button (using a macro) in my form, it asks me to Enter Parameter Value userDetails.useremail


i've tried lots and lots of things so far and nothing works. i have lots of other queries which do work, but they don't reference another table - they either contain a static value or take their data from an entry box.

can anyone give any reason why this should not work? it seems straight forward enough!!

thanks for any help

View 4 Replies View Related

Filter By Form Does Not Work

Apr 15, 2005

Hi,
I have a form which I can't seem to filter by form. When I click on "Filter by Form", the only combo option I get is "Is Null" or "Is Not Null", it does not give the full list in the table to choose. Can anyone tell me what's wrong with my form? The form also has a sub-form....does it matter?

I also tried tesing filtering the table with query but it also doesn't work.
My query,
SELECT DOCUMENT.TITLE
FROM DOCUMENT
WHERE (((DOCUMENT.TITLE) ALike "*work order*"));


Also, with Filter by form can I use wildcards such as entering in the search field of the form as "= "*work order*"

thanks,
Galantis

View 1 Replies View Related

Form Suddenly Doesn't Work

Aug 26, 2004

I have a form that worked when I left work Tuesday. It is supposed to look at all the information in my "courses" table in descending order by year, ascending order by date, in edit mode. There is also a look-up list that allows me to select the date of the course and jump to that record.

I came in today and it doesn't work! The form LOOKS like it's in add mode--completely empty. the data is still in the table. I checked all the properties and they all look right. The query still looks like it did when I left. It just quit! When I use the look-up list the dates appear in the list, but nothing happens when I select it.

I know I'm not giving you much to go on, but any ideas?

View 1 Replies View Related

Getting A Hyperlink To Work When Clicked On A Form

Apr 8, 2015

I,m trying to get a hyperlink working on a form.I've used some code I copied from another access user I found online to browse for the required file. I can't open it though in form view. The link to the file is below.Also is there a way to display a hyperlink to the file as a symbol or e.g. ... (some full stops) to reduce the form box size and hide the complete file path.URL...

View 2 Replies View Related

Cannot Get Query To Work With My Date Select Form

Jan 5, 2006

With the imense help of some of the people in this forum, I have scraped together a nice little database.
There is one function, however, which is not working.

I have a query that should run from a form I have created. The form has multiple criteria and all seem to be working except for the date fields... "First Order" and "Last Order".
The way I have it set up is that I have a table of dates. The form has a list box of the dates (first of the month, for 4 years).
The source data in the form reads: SELECT [TDateSelect].[StartDates] FROM TDateSelect ORDER BY [TDateSelect].[StartDates]; (for each of the 2 "minimum" dates)
and SELECT [TDateSelect].[EndDates] FROM TDateSelect ORDER BY [TDateSelect].[EndDates]; (for each of the 2 "max" dates)
The user is allowed to select a date range for the first order and the last order. (4 dates altogether).

The form looks fine.

In the query, I have set the criteria for the last date as:
Between [Forms]![FSetOtherCriteria].[MinLast] And [Forms]![FSetOtherCriteria].[MaxLast]

When I run the query (even with a wide date range) I get no results. When I remove the dates from the query, or enter the limits manually, the query works like a dream.

Any ideas???

View 3 Replies View Related

General :: Started Form Doesn't Work?

May 24, 2015

I have a DB: This DB was sent to me via eMail (dropbox). It consist in a BE and 3 FEs. In each of the FEs I have a started form with some code activated by the Open event.

The issue: Access do nothing when I open the FEs. Note please that the DB is designed by me and it work very well on the user"s computers.

I use A2007 on Win7

The DB is in a trusted folder. Compact and Repair is not useful.

View 14 Replies View Related

Modules & VBA :: Switchboard Form Doesn't Work

May 27, 2014

I have developed a project in Access 2003 and it works absolutely fine. There is a switchboard form too and that works fine as well. But now in our company we are moving all the projects developed in 2003 to 2010. And so I have noticed the switchboard form doesn't work in Access 2010 the way it works in 2003. The Switchboard form doesn't display all the options in 2010.

View 5 Replies View Related

Queries :: LIKE Filter Does Not Work From A Form Field

Jan 14, 2015

I am attempting to use a form field as the source for a query filter criteria. Everything works fine if I simply use an "if equal" filter condition. As soon as I try a "like" condition, nothing works.

I created a test table with just one column (fld1). The table contains three records with the following values: BRDODS, BRD, TLAODS.

The following hard coded query returns two records, as it should.

SELECT Table1.fld1
FROM Table1
WHERE (((Table1.fld1) Like 'BRD*'));

I also created a test form (Form1) with just one text field (Text0). My intent is to soft code a criteria value via the form field instead of hard coding the query, as above. When I populate the form field with BRDODS, the following soft coded query returns one record, as it should.

SELECT Table1.fld1
FROM Table1
WHERE (((Table1.fld1)=[Forms]![Form1]![Text0]));

When I enter LIKE "BRD*" in the form field, no records are returned. I should get two records, just like the hard coded query above.

I've tried all variations of the LIKE statement in the form field, but nothing works.

View 8 Replies View Related

Queries :: Cannot Get Crosstab Query To Work In A Form

Dec 30, 2013

When I run this query

TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt
SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
FROM [rpt LEAD TYPE SUCCESS RATE2]
GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;

I get valid results.

When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.

I am running MS Access 2010 on a Window 7 pc.

View 10 Replies View Related

A General Form Structure/work Question (LONG)

Aug 24, 2005

hello all, I am very new to access (my background is in VBA Excel, but I am not using VBA for access yet).
I was wondering if some of the more advanced people can give me pointers on how to accomplish (or work my way) to make the kind of form I am trying to do.
What I am trying to do is the following:
I have a vending company with couple of products such as espresso, latte, black coffee, etc.. (all of the products are sold at the same price for each customer, so I might sell the products for company A for .75, but for company B for .55 etc) and I am selling these products to many clients company A, B, C, D, etc. As A start I created a table with my products, then I created a table for my clients (this one includes name, address, contact person, and machine number ).
I would like to make a readout input form where it would get the customer name, machine number, each of the product's total for the readout period (this is usually every week), and the date of the readout. Now I would like the form to add a record in a table for the client machine number that was selected and maybe also in another table for the overall machine ( I am not sure how queries work, and weather I would need that or I can use queries for it).
My ultimate goal is to be able to do the following with the data:
- check (and perhaps graph) the overall statistics for a each of the products for all clients
- do the same for a certain client
- do the same for a certain machine at a client
- check the overall sales of all products to all clients for each month of the year
- do the same for a particular client or for an individual machine
- do the same but for each year

I have done all of this in a VBA Excel based program but I was hoping to change the program to be access based instead of excel based, since this would make the data more centralized and easier to maintain, backup, check, and update.

So if anyone can post any hints or suggestion about how I should build my tables, the usability of quarries, and especially the construction of the forms I would very much thank you for that, since these are my first days getting to know access and I am absolutely confused with it, all the language you are supposed to put into text boxes, combo boxes etc....

Also if anyone knows of any good links to some online tutorials would be great, since most for the tutorials I found on a search was for constructing a for or a query using the wizard, but with no real discussion of how to bound the data to certain fields etc.

Layth
I thank you all in advance.

View 4 Replies View Related

Forms :: Open Form To New Record / ComboBox Won't Work

Sep 25, 2013

I have a database with a Supply Receipt Form. The first field in the form is a combobox for the user to select a material/supply. I'd like for the form to open to a new record every time. When I use:

DoCmd.GoToRecord , , acNewRec

in the Form On Open property, I lose the ability to select the material/supply. Same thing happens when I set the Data Entry property to Yes.

View 3 Replies View Related

Modules & VBA :: Get Open Args To Work While Opening A New Form

Jun 25, 2013

I have tried a few times to get open args to work while opening a new form.I never could get it to work.

View 4 Replies View Related

Forms :: Query Doesn't Work To Filter In Form

Nov 4, 2013

I've got a single form ("Lead Data") that has Cascading Combo boxes that work perfectly, entering data into "tblLeadData":cboMatterTypeIDcboMatterIDcboAttyIDcboPlglID Attorney & Paralegal are the people assigned to the Matter. My problem is in finding a way to allow a specific Attorney or Paralegal to filter for only his or her records. I made a query of tblLeadData that works perfectly as a query, but when I use it as a filter in an "on click" macro event, it doesn't work. I suspect it's because of the cascading combos, because I've successfully used this kind of query based macro filter in the past.

Okay, more on how it is set up. The same people are always assigned to a specific matter, so when you pull down the Atty & Plgl combo boxes, there's only one person. So it isn't a true Parent/Child relationship, but it's working. And there were two advantages of this set up over an autopopulate set up (which I considered): 1) When I change something in the reference tables (refAtty and refPlgl), it also changes in tblLeadData & 2) in case there's an exception to the usual assignment pattern, we can just leave Atty & Plgl blank and put the correct assignment in a text box called "AssignmentNotes."

So my query of tblLeadData that works, qryLeadDataAssign, uses the following fields:

tblLeadData.AttyID
refAtty.Atty
tblLeadData.PlglID
refPlgl.Plgl
tblLeadData.AssignmentNote
Expr1: [Atty] & " " & [Plgl] & " " & [LeadAssignmentNotes]Criteria: Like "*" & [Who?] & "*"

The Join Properties in the query between tblLeadData and refAtty is set to "2: Include ALL records from 'tblLeadData' and only those records from refAtty" where the joined fields are equal." And the same for Plgl.

When I run the query, it asks me a single time, "Who?", I put in the name and it pulls up all instances of the name from any of the 3 fields. It acts as a "contains" filter, not an "equals" one.

As for my cascading combos, here are the settingsMatterTypeIDRow Source:

SELECT refMatterType.MatterTypeID, refMatterType.MatterType, refMatterType.[MatterType] FROM refMatterType ORDER BY refMatterType.[MatterType]; On Change Event:Me.cboMatter.Requery
MatterIDRow Source: SELECT tblMatter.MatterID, tblMatter.Matter FROM tblMatter WHERE (((tblMatter.MatterTypeID)=[Forms].[LeadData].[cboMattertype])) GROUP BY tblMatter.MatterID, tblMatter.Matter, tblMatter.Matter ORDER BY tblMatter.Matter;
On Change Event:Me.cboAtty.Requery

[Code] ....

I put a button on the form and put an embedded macro as an "On Click" event. The macro is an "ApplyFilter" and the filter name is qryLeadDataAssign. When I click on the button, I am asked to enter

data 3 times:Enter Parameter Value: Atty
Enter Parameter Value: Plgl
Enter Parameter Value: Who?

Clearly, the expression in the query doesn't function in the button. And the result, no matter what I put in, is that all of the records are still there, although the filtered button is activated.

I tried putting the expression from the query into the macro builder window, but I for sure don't know what I'm doing there and haven't been able to make it work.

View 4 Replies View Related

Forms :: Record Navigation In A Form Fail To Work

Jun 2, 2013

I have a data base in which there is a table where each record describe properties of one fishing trip. The first column in the table is a number (unique but not autonumber format) and I already made 10,000 records with only this index number . Now me and others in my lab are entering data to this table for each of the records.

The data entry is done using form which show one record at a time (lets call it "sail_info_F").

I made another form which has only two elements:

1. text box in which I enter a number
2. button which opens the data entry form ("sail_info_F") showing the record with the same index number as the number I enter on the text box.

(the code for this button is "DoCmd.OpenForm "sail_info_F", , , "[RECORD_NUMBER]=" & Me.TEXT_BOX_NAME"

So far so good and it does open the form in the wanted record. BUT when the form opens I can't use the buttons I have placed in this forms which move between records. Those were made with the wizard, and are working OK when I open the form directly without using the second form (The one which opens "sail_info_F" on a specific record).

View 2 Replies View Related

Queries :: Wildcard Search Does Not Work With A Form Control

May 24, 2015

I am trying to get a wildcard search to work with a form.

I have a query, in which the criteria is:

Like "*" & [Enter a word] & "*"

That works fine. I enter a word, and I get the few records in which the word appears.But if I try to replace [Enter a word] with a word entered on a control on a form, it doesn't work - I get all the records. This is my code:

Like "*" & [Forms]![Myform]![Mycontrol]&"*"

What am I doing wrong?

View 8 Replies View Related

Forms :: Criteria In Query Does Not Work With Navigation Form

Oct 23, 2014

how the Forms work I am trying to put some of them in Navigation Form and my commands does not work as they did in Form."Home app" is the navigation form, "Apeluri_neinchise" is the subform of "Home app" and "Combo1" is the criteria based on which a query shows a specific data.

What is the proper formula for query criteria?

[Forms]![Home app]![Apeluri_neinchise].[Form]![Combo1] (I tried this but it's not workink)

View 3 Replies View Related

Forms :: Two Combo Boxes To Filter Form Won't Work Together

May 8, 2013

I have a database and a form that is based off of a simple table. The table has NO look ups and all of the formatting is text. The form is continuous and simply displays these fields. (Kind of like a company roster with name and department). I have two combo boxes at the top of this form in the header with the intent of filtering the form records by department and employee type (lets say A or B).

I have tried every way I know how to get these records to filter and they will not filter correctly. Utilizing methods I have used in multiple other databases, I set the two comboboxes to cascade based on a query in the recordsource. The vba I'm using in the after event of each combo is ....

Me.Filter = "[Brand] = '" & Me.cbobrand & "'"
Me.FilterOn - True
'Brand is the "department" and the other identical code is for Personel_Type

This filters the records but independently. So, cbobox1 filters the records to show all Brand 1. When cbobox2 selection is made it shows all the Personel_Type of the selection however the first filter is already disregarded. (i.e. When cbobox2 selection is made, it contains both brands instead of the one I've just selected and filtered in cbobox1. )

Outside of making two queries to account for each possible filter, I have tried setting a filter on the filterON, I have tried a SQL based VBA code, I have tried making two forms and attempting to set the filter on open but either way, nothing keeps the first filter selected (or the FilterON, or both SQL filters, etc). The ONLY way I have found to get it to filter both is with the filter function in Access, which is not useful as my users will not have access to the menu bars.

I have this EXACT same setup in another database and it works fine with 3 cbo boxes with an after even to set a filter for the recordsource for all three.

View 5 Replies View Related

Forms :: Multirecord Continuous Form - Combobox Beforeupdate Does Not Work

Nov 25, 2014

I have a multirecord continuous form that displays the results of a query. At the end of each row there is a combobox displaying a list of form names. The user selects one of these forms from the list and program fetches the detail data in that selected form corresponding to the row where the cursor is. Now the beforeupdate or aferupdate or onclick functions do not work. Her are the properties of the combobox as JPG files and the code of the not working function.

Code:
Private Sub Sbox_AfterUpdate()
Dim ForName As String
Dim ParName As String
Dim QryName As String
MsgBox "after update combo"
ParName = Me.Form.Name
Select Case Me.Sbox.Value

[Code] .....

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved