I'm having trouble swicthing the data type of my numbers.When I am importing the table from another database it enters as a text.When I then go to table design and try to switch it to number, it removes the decimals and switches the value to something ALOT higher.how to fix this so that it just enters as a number with the same value?
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
how do i make a command button on a bound form change the source object of a subform so that the original subfrm closes and another sub form loads in its place? Basically I want to toggle between subfrms.
You know how there's MoveFirst, MoveLast, MoveNext, and MovePrevious to go to a recordset and make changes? Well, I just want to make changes to one recordset, and that would be the one I would be currently working on. That way I don't have to run the program for every single record everytime I want to modify one thing for one record. Is there something like a MoveCurrentRecord? Thanks =)
USING ACCESS 2010..My user has reported that his caps and num locks gets switched off when entering a particular form and after testing he's right!!
I've googled this and it seems that sendkeys is/could be the culprit.I do have sendkeys in the code so how do I get around it other than not use sendkeys? Can I switch num lock on in VBA?
I am very good at excel and can program macros. I am being asked to build something in access for my company, or at least manage a build. So here is my question... If we build it without both a front and back end, is it easier to take what you have and switch it to a front and back end? Also, when and why is it better?
XXXXX-X (x are digits), for example - 4 01-1, 4 01-2...
I need find field with text beginning 4 01 and max of last symbol. Last symbol +1, then again convert to text field. For example: for example 4 01-1, 4 01-2,4 01-3 - must find 4 01-3 , then 3+1=4 and 4 01-4 format again to text.
Please, help me with formatting. Thank You in advance.
Here's a little design problem, all of my VB script works fine, it's just a design problem:
Toggle Button (onClick) { if pressed in, unlock protected field, pass focus to protected field else (if not pressed in), lock protected field }
Protected Field (lostFocus){ When Focus is lost, protect field then reset toggle button } See, there's no way that I know of for Access to tell what field has focus at any given moment. It lies inert until an event fires off then it responds. You can't wake Access up and tell it to go do something. So, Protected Field has to lock itself when it loses focus.
Here's the problem. If a user unlocks the field but then decides not to make a change and clicks the toggle again to lock the field, first Access fires the script to protect the field (locking it), then it resets the toggle button, then it registers the toggle button click which unlocks the field. See the problem? If the user changes their mind about changing the field and then tries to lock the field, it ends up unlocked. I need something that will work whether the user is good about it or whether the user is in a rush and forgets about it.
Right slightly complicated (in my eyes) and I have not found anything in previous threads to help but here goes:
I have a main form (FRM_SUMMARYREVIEW) that has an unbound subform field on it (subfrmmaster).
When FRM_SUMMARYREVIEW is first opened subfrmmaster has its sourceobject set to a form called FRM_OPENSUMMARY.
This subform lists all my open escalations, in a continuous forms format and has a field in it called "escalationid" and when I click this field "subfrmmaster" changes it's sourceobject to another form called FRM_MAINDATA and should display the data relevant to the escalationid I have just selected.
In the past I used to click on "escalationid" and it opened up FRM_MAINDATA as a new form, using the following code:
However how can I get the subfrmmaster sourceobject to change and display the relevant escalation detail ?
My other option is to have two subforms on the main form, one with "FRM_OPENSUMMARY" the other with "FRM_MAINDATA", as "FRM_OPENSUMMARY" has a list of escalations displayed, in a continuous forms format could I set it so as I arrow down the list, the data in FRM_MAINDATA displays the highlighted escalation in "FRM_OPENSUMMARY".
Understand the basics of using a cascading combo box as follows:
Table criteria for stblDiscipline set to [forms]![frmPersonContact].cmbDiscipline
and SQL statement for cmbDiscipline_AfterUpdate() Me.cmbSubDiscipline.Requery
When I switch to another record the cmbSubDiscipline displays the correct value from the table but, the selection values are left from the last Requery. Unfortunately, the combo box will also let the user select one of the incorrect values. Tried to add a _Current statement without success.
I have a tabbed form built in Access 2007. It's a series of about 32 different questions, with some being fill-in, some drop down boxes to select from, some radio buttons to choose a rating from 1 to 5. The reason I used a tabbed form was just to keep things consolidated on one screen where no scrolling was involved. All of the entries on each of these tabs are deposited into the same single table. I have 7 tabs, and all of the fields on the 7 tabs compose a single record in the table.
I have the actual tabs hidden, and instead use a button to switch to the next tab (Continue button at the bottom) once all the fields on that tab have been completed. I have all of the necessary fields set to be required, but Access does not validate the entries until the very end of the survey when they click a 'submit' button that actually saves the responses and returns to the opening splash page to start the survey again.
What I want to happen is for the fields on the current tab to be validated before it lets the user move to the next tab with the continue button. If they click continue and have left any fields blank they should get a message that all required fields must be completed or something similar to that.
I have found a thread here entitled "Form Validation before Moving to another Tab" which sounds similar to what I'm doing, but using the button method to navigate to the next tab, there is no BeforeUpdated event to assign that code to.
I have a form with a TAB control and four pages. Each control of each page has his TAB index set correctly (starting from 0 to n), some of controls are not enabled to be tabbed. The form's cycle is set on "Current page".
I have two kind of problems.
First: I am on the last control of the page, pressing TAB key the focus gets invisible. Press TAB key again and the focus moves on the first page's control. For each page happens this behaviour.
Second: clicking randomly on the TAB pages the focus is not always on the control with TAB index 0, but usually on the last.
I have read that clicking on a new page the focus should be automatically on the first control, but this does not happen in my experience.
Access 2013 on Windows 7 ..I have a Main Switchboard form which is Pop-up and Modal. Everything on it is working as required, but if I am in Design View and try to switch directly to Form view, the form disappears and the Access screen freezes - the mouse moves, but no menus or objects can be selected. If I have the VBA window open, I can carry on working in it, saving the modules and closing that window as usual. But the only way to open a form or do anything else in the main screen is to kill Access by closing the Access window in the system tray (or using the Task Manager).
If I close the Switchboard when in Design view and then open it in Form view, everything's fine.
The only recent change I can think of is upgrading from a 32-bit implementation of Access to 64-bit.
I've got this form called mainmenu and inside this form I've got this code to prevent it from being closed.
Code: Private Sub Form_Unload(Cancel As Integer) Cancel = True End Sub
Now then, when I try to switch to design view in this form it doesn't let me because of this code. Is there anyway to tweak this code so that I can get into design view without having to keep disabling it?
My issue is that one of my queries can be run, opened in SQL view, but not opened in design view. When I attempt to view in design view, Access freezes and I am forced to close the program. My database is split, FE on my local, BE on another computer on my LAN.
Below is the SQL, I tried to just copy and paste this code into a new query and I encountered the same issue.
I have a small question. There is a table that I am trying to use that houses product numbers, the only problem is that those product numbers start with a '0' but the '0' got deleted because the numbers are housed in a number defined field. If I change the table properties to text, is there a way I can add a leading '0' infront of every existing product number?
I'd like to take a branch number field, let's assume it is "223" and change it to "223 No Com". Is there a way to add the "No Com" on the end of it like that?
I have a table with the following fields: Tool Text Tool_ID Text Description Text
The Tool_ID field holds a number if the tool provides a tool id. However, if the tool doesn't provide a tool id, the text "No tool ID given" is automatically placed into the field. Therefore, it needs to remain a text field for this reason and others that are too long to go into. Anyway, I created a query that pulls only one specific tool which always provides a tool ID. Therefore, the results of my query will always have a number in the text field "Tool_ID".
Question: Is there a way to convert the text field to a number field within the query?
I have a form with four combo box that will search for a record, as follow:
Field Name "Printer" , DataType "Text" Field Name Month" , DataType "Text" Field Name "Year" , DataType "Number" Field Name "Day" , DataType "Number"
code for Find Record Button:
If Not IsNull(ID) And Trim(ID) <> "" Then StrSQL = StrSQL & " And [Id] = " & [ID] End If
If Not IsNull([txtname]) And Trim([txtname]) <> "" Then If InStr([txtname], "*") = 0 Then
StrSQL = StrSQL & " And [Name] = '" & [txtname] & "'" Else StrSQL = StrSQL & " And [Name] like '" & [txtname] & "'" End If End If
'================================'
If Not IsNull([txtyear]) And Trim([txtyear]) <> "" Then
If InStr([txtyear], "*") = 0 Then
StrSQL = StrSQL & " And [Year] = '" & [txtyear] & "'" Else StrSQL = StrSQL & " And [Year] like '" & [txtyear] & "'" End If
End If
'================================'
If Not IsNull([txtmonth]) And Trim([txtmonth]) <> "" Then
If InStr([txtmonth], "*") = 0 Then
StrSQL = StrSQL & " And [Month] = '" & [txtmonth] & "'" Else StrSQL = StrSQL & " And [Month] like '" & [txtmonth] & "'" End If
End If
'================================'
If Not IsNull([txtday]) And Trim([txtday]) <> "" Then
If InStr([txtday], "*") = 0 Then StrSQL = StrSQL & " And [Day] = '" & [txtday] & "'" Else StrSQL = StrSQL & " And [Day] like '" & [txtday] & "'" End If
End If
'================================'
This is only a part of the code, I have a problem with year and day since they are numbers not text I was told that i cant use this line:
StrSQL = StrSQL & " And [Month] like '" & [txtmonth] & "'"
I was told that there is no need to use "like", am not really sure how to do it, any help will be appreciated.
Is it possible to actually change a number to text? I cant seem to figure out how. I have 2 proprietary systems one uses a number and one uses text for the same item. I know how to change the text to a number however the more efficient way for me to do this is the other way around. Its the differnce from 300 to 400 record change vs 4M. Any help would be great thx.
here's the dealio... i need perform an aggregate funtion off of a certain field, but it contains null values. i tried using Nz and an Iif IS NULL statement to get rid of the nulls. but both methods return the values as text. obviously, i can't get an Avg of these text values.
here's the two methods i tried:
Format(IIf([AvailTab].[Per00] Is Null,"1",[AvailTab].[Per00]),"Percent") AS Per01Test
Format(Nz([AvailTab].[Per00],"1"),"Percent") AS Per00