On a form, is it possible to have #### appear in the form box when it loads and then the user would type a 4 digit number in place of ####.Also, for the table associated with that form, how do I limit account numbers to a 4 digit numeric value?
View RepliesI have a database I am using to record financial transactions. I have a transaction edit/entry form that uses combo box lists to select the different segments of my 32 digit account numbers. The issue I am running into is that when I enter a value value in the first box/field the form jumps to the very first record.I then can use the navigation buttons to get back to the last record, and all the subsequent boxes/fields work fine without jumping to a different record.
View 5 Replies View RelatedHey
I currently have this code which tells the user there is a an accout number already on the db:
Private Sub AccountNo_AfterUpdate()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select * From Spreadsheet where AccountNo = '" & Me.AccountNo & "';")
If rs.RecordCount > 0 Then
MsgBox "This Account Number Is Currently Within The Database And Will Not Be Allowed.", vbInformation, "DD Cancellations"
Me.AccountNo = Null
End If
End Sub
I want a peice of code which instead allocates a letter to the duplicate account, so user enters a duplicate account, the account will then turn into 123456789A, upto 5 duplicates are allowed, so upto 123456789E. Once "E" has been reached accountnum box will turn null with msgbox. I tried out Case statements but that doesn't seem to work.
all help appreciated. Thanks.
Hello,
I have extracted 4 digit numbers from a long strings of characters and numbers which I have identified as date. Some are inputted as
mmdd (example: 1129)
ddmm (example: 2310)
I have to calculate the number of days between the start and end dates. I tried formatting as date, but I can't get it to work.
Any suggestion?
Thanks!
Is there any code to be able to be used within a query to locate the 1,2 or 3 digit numbers?
such as 1 ,11, 111 - 2, 34, 834 etc...
I have a query to create in access based off a query already created in SQL
The SQL query converts a 4 digit number into a date using this code:
CAST(CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN RIGHT(OPDT, 2) ELSE LEFT(OPDT, 2) END + '/' + '01' + '/' + CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN LEFT(OPDT, 2) ELSE RIGHT(OPDT, 2) END AS SMALLDATETIME)
OPDT is a digit number in text format. The function converts 9606 into 1996-06-01
What is the access method in order to this?
I would like to know how to limit a field on a form to only Alpha Numeric characters.
Example: ~AAUZNTO
This would be scanned by a bar code and I want the field to show only this when scanned: AAUZNTO
I have a table that has several fields including CallID (autonumber) and SKU (text)
SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...
Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:
Code:
SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU
FROM Calls
GROUP BY Calls.SKU
HAVING ((Not (Calls.SKU) Is Null))
ORDER BY Count(Calls.CallID) DESC;
The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.
Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.
Hi all,
I am having one table with id from 1 to 50. Now I want to return random records from that table using rnd.The point is that is there any funntion like
Select [id] from tblids where id not in (values) with rnd
Thanks in Advance
Dana
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
Hi,
How can I get a 2 digit month when I use month(now())? I've getting only 1 instead of 01. Please advise.
I have a field “Ref Number” which is number 11digits long. I want to double the value of ever second digit (starting with the first digit)
So:-
8 7 2 5 6 0 0 2 1 2 2 (There is no gaps in actual data)
Would Be:-
16 7 4 5 12 0 0 2 1 2 4
Thank you
Hi there
I have prop in checking 7 digit numbers.
In my form i have SNo field. This field should get range of value is from 999999 to 9999999(that is only 7 digits). How i can write code in Acces. I am new to MS Access. If i enter wrong number(ie lessthan or greater than 7 digit) , other field should not accept the value or cursor shouldnot move from text box.
i created a small project in Access how i can make username and password.
thanx in advance
by
yasi
I'm trying to extract the day from a date field but the day has to be 2 digits. For example, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, etc. The following extracts the day but in single digit for days under 10:
=Day([FromInclusiveDate])
How do I extract a 2 digit day?
Any help would be appreciated.
How would I replace the last digit of all the numbers on a table field from a 1 to a 2? Please help. Thank you.
View 2 Replies View RelatedHi, when I type 0600 in a text box, it dispays it as 600. How do I make it so it displays the number that I actually type in, with a zero as the first digit?
Please help. Thank you.
Afternoon all,
I have another account on this forum - dickohead, but I am unable to post or reply to any topics with it.
Can someone please help me with this?
Dickohead.
Good day,
I need a textbox to a five digit number. How would I write code to reflect this.
Thanks.
Hi,
After I setup user-level security, and add the new users with Admins permission.
Why I cannot delete the Admin account?
Can we delete the Admin account forever after setup user-level security?
Please let me know about it.
Thanks.
Ok here is my question. I have two column that I am trying to merge. The first column I have set to be two digits. The second column is set to be three digits. The problem is if the first digit in the second column is a 0 it gets dropped once the two columns are merged. How do I stop this from happening. I need the merged columns to be 5 digits.
Thanks,
Hi all, I am happy with my fax database until my goverment decided to add another 6 infront of all the phone and fax number.
How can I change all the numbers together?
Example from : 1234567 to 61234567
Thanks.
I have a query Im working on in the query are several fields. 2 fields (cat1 and cat 2) have nothing but 3 digit category codes (ex- 333, 334, 335 in the and some null values. Another field is the quantity of items I have which must be >0.
I have 7 codes I want to delete from cat1 and cat2 so my guess is I would place Not "333" And "334" And "335" in criteria for mcat 1. I also need to do the same for cat2. So I would apply the same criteria in the same row under cat2. So heres how my query would look in design view.
cat1 | cat2 | quantity
Not "333" And "334" And "335"| Not "333" And "334" And "335"| > "0"
Its not working, I can only seem to do one "Not" in all of the fields. So If I do Not "333" in cat1 I wont be able to do anything in cat 2 nor will I be able to add 334 to cat 1 or 2. I have tried Or it doesnt work I have tried using different colums and different rows.
There has to be a way to delete the same criteria from 2 different fields for multiple items per the same field.
Any thoughts? Much appreciated!
hey guys i have the following line in my query for removing seconds from a time/date stamp and all works great except one thing if the time is 10:02 it is showing as 10:2 anyway of getting it showing correctly?
(Day([HTRXTBL].[HTRX_TIMESTAMP]) & "/" & Month([HTRXTBL].[HTRX_TIMESTAMP]) & "/" & Year([HTRXTBL].[HTRX_TIMESTAMP]) & " " & Hour([HTRXTBL].[HTRX_TIMESTAMP]) & ":" & Minute([HTRXTBL].[HTRX_TIMESTAMP]))))
I have a table that tracks call-in's and tardies. Sometimes, a person will have a tardy and not a call-in and vice versa.
I need to perform a calc on both the call in and the tardy fields. Is there a way to tell the database that if [Tardies] is blank (nulll), that I want it to put a zero in the field? I know this must be done by a query, but can't figure it out.
I've also tried splitting these two tables into a call-in's and a tardies table, but can't get a query that will show all tardies AND all call-in's. It will only show either all call-in's with associated tardies or vice versa, so if a person has no tardies, but does have call-in's it won't show them. What can I do? Thanks!
KellyJo