I have a form that asks 2 questions. Date and Account. I need to check a table to see if the combination of both date and account have not been used. Date is normally today, can be yesterday. Account has a drop down menu with 6 choices. If both the date and account have been used then there should be an error shown to that effect. If not used, then proceed on to the next form for data entry.
Can this be done? And how?
I am trying to validate two fields in a form against each other. Neither are required, but If one is filled in, then the other must be filled in. If neither is filled in then they can both be blank. It is for a time, and description related to that time.
So if they enter in 5 hours, they have to type in the description field describing those 5 hours. If they type in the description field, they have to type in the hours. Vice versa. Or they can both be blank.
Right now I put in the table properties validation rule
([Other Description In] Is Null) Or ([Other Time In]>0)
This works half way. It stops them from putting in a description without time. but they can still put time without a description.
I can't figure out how to do multiple validations of the same two fields. If I do an And or another Or statement it breaks the whole thing and they can enter with no restrictions.
Also, I tired to do it at form level for the other half (to stop them from putting time without a description) but it brings up a pop up and they cant tab to the description field to enter a description without putting time back to "0" first.
I am trying to put in a validation rule so that data in one field cannot be less than that in another.
e.g. i want to make a validation rule so that a phone call end time cannot be before the phone call start time.
Can anyone help???
I have taken over a database that has be modified by a number of people over time. I have a forw with tab controls and a some of the fields are critical to be completed before you should be able to progress to the next tab. There are currently validation controls on the controls on the first tab, but if you only complete the first of these controls it is possible to move on to the second tab. The third tab is a subform and and if you try and select this tab, the validation text appears, but you can still switch to that tab which I want to prevent.
Finally, I have deleted one of the controls which was a critical field for completeion. I had assumed the validation was based on the validation on the control and would therefore be deleted but I still get a message saying complete this. I can't work out where there miight be code to make this happen? I'm not sure if anyone can help me with this without seeing the database, but I just wondered if anyone had any ideas what to even look for to help me understand this.
Sorry if all this is a bit vague, but would really grateful if anyone can help me at all.
I'm trying to figure out how to validate a field to make sure the users are not entering any blank spaces into the field. I've tried setting the mask up as "aaaaaa" but it still lets the user enter a blank space.
I've also tried building an event procedure to run on exit that reads:
Private Sub Barcode_Exit(Cancel As Integer)
Dim LPos As Integer
Dim LChar As String
Dim LValid_Values As String
'Start at first character in Barcode
LPos = 1
'Set up values that are considered to be alphanumeric
LValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ+-.0123456789"
'Test each character in Barcode
While LPos <= Len(pValue)
'Single character in Barcode
LChar = Mid(pValue, LPos, 1)
'If character is not alphanumeric, return FALSE
If InStr(LValid_Values, LChar) = 0 Then
AlphaNumeric = False
Exit Function
End If
'Increment counter
LPos = LPos + 1
Wend
'Value is alphanumeric, return TRUE
AlphaNumeric = True
If AlphaNumberic = False Then
MsgBox "Barcode cannot contain blank spaces, only letters and numbers.", vbExclamation, "Barcode Field Value"
Else
End Sub
****
However the message box does not come up and the form will not then let you get out of that field.
Any ideas? I've been checking on this field by running a query and then manually correcting the errors but I would rather that the DB check and force correction at the time the data is entered.
Any and all help will be greatly appreciated.
Charis
Dear experts,
I'm quite new to Access but find it very interesting to learn more. I used to create data entry in other softwares but now like to use Acesss instead since it has many advantages i.e can be used for web survey.
My question is how to create the routing/ jump & how to check the relationship across fields for the same record right from the entry (i.e Age=18 ---> jump to "smoking"; "drinking"=yes --> "Age">25)
Any advice is really appreciated.
Thank you so much,:)
Hop
I work on a pre-created Access database, and the other day I was working on it, and was trying to export something to Excel to sort it and do some Pivot analysis.
Anyway, I must have pressed something, because now every time I open the database, rather than saying "record 1 of 20463" and showing the data from record 1, it shows "record 1 of 1" and all the data fields are blank. If I go to "Records" and "Show All Records" they'll all come up, but I don't want to have to do that every time, and as I import and export all the time, I'm worried that the next time I try it it'll mess up the years of data I have.
I have a Company form, a typical form with fields for capturing company names, address, phone etc.
The two fields I have an issue with are:
CompanyName: a regular text field for full company name
ShortName: a regular text field for same data as above but with spaces/blanks removed (to be used as a 'quicksearch' field)
I want the ShortName field to be automatically filled when the CompanyName is entered (on a new record), but allow the ShortName field to be overwritten by the operator.
I would then like to "lock" the ShortName field, such that any subsequent amendment (accidental or otherwise) to the CompanyName will NOT automatically update the ShortName field, but there will still be occasions when legitimate changes to the ShortName field will be required.
Removing the spaces is not a problem; I have a problem determining how to control the automatic updating and subsequent updating/prevention of updating.
Suggestions please.
regards
AlanM
how to be able to enter time in access form the same way as if would be an access table (1p = 1:00 PM; 1.25 = 1:25 AM etc)
View 1 Replies View RelatedI have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.
SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName
FROM Staff INNER JOIN myTable ON
Staff.ID = myTable.StaffID
WHERE myTable.myDate >= #3/2/2014#
AND myTable.myDate <= #3/3/2014#
AND myTable.myTime >= #8:00PM#
AND myTable.myTime <= #11:00PM#
ORDER BY myDate desc
In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.
I have a form that creates quotes for my company by inputting inventory items in a continuous subform. I need to be able to check and see if the item already exists in the inventory or if it is a new one that has been manually entered, and have this toggle a bound yes/no field that I will use to control whether or not some fields on the form are locked. Here is the code I have been using, but it doesn't seem to work at all.
Private Sub Combo14_AfterUpdate()
Dim itemcheck As Integer
itemcheck = DCount("[Item]", "tbItems", "[Item] = " & Chr(34) & Me![Item] & Chr(34))
If itemcheck = "0" Then
Me![Locked] = False
Else
Me![Locked] = True
End If
End Sub
I would appreciate anyone's feedback on this.
All I really need some help.
I have two tables:
Table 1
Table 2
There is a one to many relationship here.
One in table 1 and many in table 2.
When I add in a record in table 1, I need a validation script/key that won't allow adding a record to table 1 without a corresponding record in table two.
Please help. I'm really stuck. I am doing this through a form. I tried messing around with the "requirements" but had no luck.
I need to validate a surname field which obviously can only consist of letters and occasionally spaces and/or hyphens. (i.e. van Driel or Johnson-Crooks)I managed to create a validation rule which would allow me to have space in the surname field:Is Null Or Not Like "*[!((a-z) or ( ))]*"but when I tried to allow hyphens into the field - I used these codes - but none of them worked:Is Null Or Not Like "*[!((a-z) or ( ) or (-))]*"Is Null Or Not Like "*[!(a-z)]*" Or Not Like (" ") Or Not Like ("-")Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[!( )]*" Or Not Like "*[!(-)]*"Is Null Or Not Like "*[!(a-z)]*" Or Like "*[!( )]*" Or Like "*[!(-)]*"Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[( )]*" Or Not Like "*[(-)]*"Is Null or Not Like "*[!(a-z)]*" or (in ("-"," "))Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*"))Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*")) And (In ("-"," "))Is Null Or Not Like "*[!(a-z)]*" Or Like ( ) Or (-)Please can smeone help me.James
View 3 Replies View RelatedHello all.
I'm having a problem with a query. I am pulling information from a table that holds demographic info plus some company info. the data in question is the deptno of an employee. This employee also has a jobcode associated with it. I have another table with a list of a few jobcodes.
The data I will retrieve is fname, lname and deptno. If a person is in detno 210 then I want to check his jobcode in the jobcode table. If it is in the table then I want it to return 215 for a deptno. If not then it will return 210.
Does anyone know how this query would look like?
Thanks in advance.
Question I am looking at some annoying Access database for a charity and got stuck on trying to make something work that they believe used to
Two Tables
Venues
pK Venue ID
Venue Name
Venue Capacity
Booking
pK
Venue
Name
Addr1
Addr2
Addr3
Ect
They have a query call capacity check which does the following
Count Occurrences of Venues then takes this away from venue capacity
Essentially they have a standard access form of the Booking table with a drop down box and want it to only validate if seats are still free at a venue eg if the current count for venue is less than the venue capacity.
Any ideas ?
Hi all
Im currently trying to set validation rules for a form using VBA, does anyone know of a good method of doing this as i'm a bit stuck:confused:
I have a NEW INVOICE button, and the code behind says
DoCmd.GoToRecord , , acNewRec
Now if a user clicks on this buttons 3 times, three blank records get inserted in the table. THis is because I am generating teh invoice number myself, and the invoice_date is defaulted to current date (DATE())
The script written in the Sub Form_BeforeUpdate event to validate if the invoice is empty, if the total is ZERO etc. is all skipped, and I get blank records in the table. I even tried to place breakpoints in the before updates script, but it does not even come inside.
What is the right way to trap data errors, blank records in this situation?
Please help.
Thanks,
Vinai
Hey,
I've been looking at this website (http://learning.north.londonmet.ac.uk/ib212/week7/validation.htm) and i'm trying to find an input mask that lets a user enter in an I.P. address like the IP settings in Network Connection > Properties > TCP/IP > Properties.
E.g. a user enters 19216802. I want this converted to 192.168.0.2 without the user having to enter any '.'
So far this is the closest i've gotten to validating the field
0##.0##.0##.0## whereas 0 means something has to be entered and the # are optional.
Thanks,
Barret
Hey,
In a form I am trying to complete, I want the user to be able to enter in an IP address like so
192168<user hits space bar>0<user hits space bar>1
Would convert to - 192.168.0.1
Is there an input mask which allows this to happen?
Thanks.
Before I start implementing this, I would like to know if it is possible to do in principle
I want two fields, say TimeStarted and TimeEnded. I then want to subtract the two from one another to see how long the task took.
Is this possible or would i be wasting my time trying it?
Can you calculated time in a query?
Such as hours during any given day?
I have a form generated from infopath where users enter the amount of time it took them to complete a task. It is set up right now in time format, so a user would enter Task A-0:15 (to show 15 minutes). Users have multiple tasks with different times and I would like to be able to show a total for all those tasks combined.
Task A-0:15
Task B-0:45
Task C-1:30
Task D-0:30
Total = 3:00 <------This is what I would like to do
I have an Access 2010 database that tracks Classroom Training. I have a "Start Time" field for each class and a "Duration" field for the length of each class. The Start Time field is a date/time data type and the Duration field is a number data type set to Double with 1 decimal place.
Example:
Start Time = 8:00
Duration = 1.5
So the class should have an End Time at 9:30. I have been able to convert the "Duration" field to a time field for example 1.5 now displays 1:30.
My question is how do I add the 2 fields together to get an End Time of 9:30 ?
HiBeen trying To Find A Way Of Validating A Postcode.But Getting No Where Being They Can Be Like CT1 1QX, CT11 2DT, CT11 12DT Act.Best I've done is to make sure there's a space and it starts and ends with a Letter.Any Help Advice Would be much AppreciatedMick
View 2 Replies View RelatedHi there, just a quick question really.
I have got a start date and an end date field in a table both in Date/Time data type. Can i validate the end date so that it cannot be longer than 5 days after the start date, that has been entered. Or is this impossible to do in a table?
Thanks for any help
Hi
I have a large file where I am trying to clean up the data.
One of the fields is the email address.
I want to check if the email address has something followed by an @ symbol followed by something else. If it does not, then I want to replace it with a null value.
Can you help?
I thought that I could use the wildcard features such as <>*@* but this does not appear to be working.
Noel