I am trying to find the position of a "," in a data field.
I was trying the SEARCH function I found in Help "SEARCH(',',[emplname])", but I am getting "Undefined function 'SEARCH' in expression." when I run the query.
Any ideas?
Thanks,
Jeff
I am trying to find the position of the second space in a string, but have not been able to find a solution. Any suggestions on how I could do this using an expression in the QBE window or VBA would be appreciated. Thanks in advance.
View 8 Replies View RelatedHi,
I am looking for Idea's of how to create a report.
Basically the report is a league table showing Month and YTD, this is the easy bit.
The part I need suggestions and guidance on:
The report also needs to show peoples movements in the league table i.e: whether they have moved up, down or not moved at all from the previous months position.
I was thinking of creating 2 tables: Current Month + Previous Month, in each table the persons position is indicated (an ascending record number is created throughout the table - not sure how to do this yet)
Then using code, lookup each persons position in previous month and write that position number against the persons UID and current position in current months table.
Am I on the right tracks or does someone have an easier solution?
Thanks in advance.
Darrell....
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
View 10 Replies View RelatedI have several comboxes where I'm using date/time input masks. When I go to enter data in the field the cursor is situated at the right end of the box. I have to backspace to the beginning of the field to enter the data. How do I position the cursor to the beginning (left side) of the field?
View 3 Replies View RelatedHi!
I have a field that has to be 20 Characters long. Most of the time data for this field is less than 20 characters, so the remaining has to be filled with spaces to make it 20. e.g. if there is a record xyz1234567, then the rest of the record should have 10 spaces like xyz1234567ssssssssss, meaning if i click on this record then the cursor should not blink after 7 but after the spaces. Hope I am making sense.
Thanks for any input.
I have a field which looks like this - "d123456", I will have to display the field without the "d" in front, meaning I will need to show "123456" Any idea how I can do this? Thanks!
View 4 Replies View RelatedI have a large table (over 20,000 records) where the text fields were all set at 255 - even those requiring a single character entry. Is there a way to determine the highest existing character count for each field so I can set the text fields to a reasonable setting? After a compact and repair will existing records be set to the new setting?
View 1 Replies View RelatedI need help with a required field that has different character lengths.
I have an input mask that covers the first 7 characters, however, the users are still entering the incorrect policy numbers, I need to put a stop to it.
I have 3 different policy number lengths involved, 7, 8 and 9.
They are different kinds as well, alpha, alpha/numeric and numeric.
Example below:
P###### - 7 characters
02###### - 8 characters
02M##### - 8 characters
NX####### - 9 characters
HX###### - 8 characters
HXL##### - 8 characters
HXM##### - 8 characters
My question is this -- do I have to create 7 different forms or can I program it to accept only the certain (policy#) characters above. Or can I create a query to insert into my form.
Any help is appreciated, Thanks as always.
I need help with a required field that has different character lengths.
I have an input mask that covers the first 7 characters, however, the users are still entering the incorrect policy numbers, I need to put a stop to it.
I have 3 different policy number lengths involved, 7, 8 and 9.
They are different kinds as well, alpha, alpha/numeric and numeric.
Example below:
P###### - 7 characters
02###### - 8 characters
02M##### - 8 characters
NX####### - 9 characters
HX###### - 8 characters
HXL##### - 8 characters
HXM##### - 8 characters
My question is this -- do I have to create 7 different forms or can I program it to accept only the certain (policy#) characters above. Or can I create a query to insert into my form.
Any help is appreciated, Thanks as always.
I have a field called "SC" with a value 13-251. I need to remove the "-" ending up with 13251. I tried using, as a starting point, Mid(String, Start, Length) with no success. I found this in another topic, and thought this might be close to what I was looking for. I sure did not know how to use it. When I selected the run command I got the following response:
Query must have at least one destination.
I tried a few different things like adding an extra field and different pointers but I doubt if I'm even standing in the batters box.
I have a column in my table, and its size is set to 10 characters. In a form, i have a combobox based on that column, which contains the values "Corrective", "Preventive", and "Supplier". both Corrective and Preventive are 10 characters, and fill up the field to its max, however, Supplier is only 8 characters. When I look back in the table data, there are two spaces after Supplier. Basically, I can use the arrow keys to "see" the two blank character spaces. Why is that? Are these extra blank spaces taking up space they shouldn't?
View 3 Replies View RelatedIn my table, I have a field for "notes" and have it set to long text. It works just fine and has all the text visible. When I create a report or a form from this table, it truncates the text in that field. It limits to the 254 characters of old. I am not sure how to fix this. I have tried setting the WillGrow to yes and it still does not work.
View 4 Replies View RelatedHow can I adjust my required field so that you cannot simply enter a space bar character to circumvent the requirement?
Here is part of the code:
If IsNull([txtRequiredReason]) Then
MsgBox "The Reason field is required"
DoCmd.GoToControl "txtRequiredReason"
Else
I am building a database for a client who manufactures car parts. I have a table to store vehicle model. Each model has a date range of production. I have two build dates from and to. Both of these fields are setup as date fields on the table. I made both fields required with a mask for validation. In the "build date to" field if a car is still in production I need to leave the field blank. Is there any special character that will allow me to store a required field blank.
View 2 Replies View Relatedwhat do I do so that when I type into a field the first character becomes an Upper case automatically.
View 1 Replies View RelatedI have a very experienced colleague who has Access 2013 and has the following issue:
For some reason, Access keeps recapitalizing the first character of the field names when I save, close, and reopen the table in design view! I just spent several hours lower casing prefixes only to discover after I ran a routine to extract the field names that they had been recapitalized!
I've fiddled with turning off Autocorrect, but that seems to do nothing positive for me. How to turn this feature OFF?
I am trying to create a DB that would calculate the totals of different projects and also calculate the global total (i.e. the total of all the projects combined together)
So far I have created a query that can calculate the total of the projects in a field which I have named Total1. But now I want to calculate the sum of the field Total1, i.e. add all the totals of the projects up.
Sorry my explanation does seem quite lengthy, any help would be much appreciated.
ia.:)
I have a form where I want to enter an ID and then see the Name in the next field.
How do I do that? Everytime I enter an ID I get "?Name" in the next field.
The Table is "BudgetLine" and I enter an ID from a table "Item"
Item
ID
Name
I have a file that I need to import on a regular basis. There are two different issues with the import:
1. the first digit in the file indicates if it is a record I want to keep. In this case a 1 indicates a repair record and an 8 indicates it is just information such as email, contact info etc.
2. I have a field in the file that may change the import specifications
For example:
If the record is a Non wheel repair then column 167 is a 50 character description
If the record is Wheel repair then column 167 is a 28 character description and the remaining 22 characters are broken down into 10 different fields
So I need to do the following
1. Read the first character in the line and determine if it is a number 1 and if not discard it
2. Read a field in column 109 that is two characters long and if it says it is a wheel repair the import will break column 167 -216 down to the appropriate 11 fields and if it is a non wheel repair it will import columns 167 - 216 as one big description field.
I have used the import specifications with Access but it seems this will have to be done in code in a module or something and I am not very good with VB. (only know the basics)
One other issue is I have dates in the file that are 130225 and 1302 (so full date and then Year/Month) if i take out the / date separator in the import spec the full date works but the Year/Month doesn't.
Hi ,
I have developed a very basic contact DB to record enquiries coming into my business. The last field on the DB is for comments, so that anyone entering the DB can see the latest conversation we have had with a particular customer. What I want to do is create a means whereby I can find a customer within the DB and then be able to add the contents of our last conversation into the Comments field. Would there be any way of doing this via a switchboard???
I am a very inexperienced Access user so the simpler the answer the better. Many thanks in anticipation
Keith:(
The last field in my table is 'Comments'. I want to find if any record or records with 'Comments' field empty. I created a query and added all fields from my table. In the criteria section I typed Like "null". But the query is not returning records where 'Comments' field is empty. I have deliberately added (for testing) a few records leaving comments field empty. This can be viewed clearly in Table view. But the query I made is not displaying the records. Please help.
View 2 Replies View RelatedI have a form that runs a report with two text boxes, one combo box, and a button.
The text boxes hold dates and SQL feeds them the oldest and newest dates from their fields (Date Received and Date Taken). They work fine, when the form is opened, the fields are filled and if I change the dates, the report pulls back data from the correct range.
The combo box is called Analyst Name and this is the row source within the parenthesis (SELECT [PickList-AnalystName].ID, [PickList-AnalystName].[Analyst Name], [PickList-AnalystName].[Analyst Manager] FROM [PickList-AnalystName];). The bound column is 2. The correct names are displayed, it looks like it works fine but then there's the button.
The button is named cboAnalyst. Enabled - yes. On Click - Event Procedure. This is the code with the backslashes added:
option compare database
//Private Sub cboAnalyst_Click()
Dim strCriteria As String
If IsNull(Me.[Analyst Name]) Then
strCriteria = ""
Else
strCriteria = "[Analyst Name] = "" & Me.[Analyst Name] & """
End If
DoCmd.OpenReport "Ad Hoc Reporting", acPreview, , strCriteria
End Sub
If I run this without a name, I get back all of the data within the right time frame. Once I put a name in, the report comes back empty, showing 0 for the count. I don't even get an error, I just get that empty report.
I have a table with about 20.000 records.
From this table I would like to "search" in a column the field that holds the text with the most characters.
Is there a way to find this with a formula or expression?
Hi
I have a table called SWAPS which contains information on each user and the different equipment he has been issued. Query at the moment contains 5 fields
Payroll, Surname, Firstname, Mobile No and Date sent.
SQL for this query is as follows
Select Swaps.PR, Swaps.surname,swaps.firstname,swaps.[mobile no],Max(Swaps,[Date Sent] as Lastdate
From Swaps
Group by swaps.pr, swaps.surname,swaps.firstname,swaps.[mobile no]
Order by swaps.pr;
Now when I run this I get the results I want by date so I get
123456 Smith Fred 0788411025 12/3/8
456789 West Bob 012555447854 13/1/8
So it correctly ids all the last dates for me as I want to see the latest issue date for that user. Now I want to add in another column to this query which contains the serial number of the equipment issued and heres where the problem lies. If a user has been issued more than 1 piece of kit, I get all the serial numbers irrespective of date. How do I get it to display the serial number for the latest date?
Thanks
I'm looking for a way, when entering form, to force a field to accept a value, rhs;jjc;cjs. then every record will automatically contain the accepted value time you go to a new record. That would only be for newly added record. It would stay this way tell exit forum and the next time is open. It starts the same way.
It's my way of finding out who entered each record.