I have created code to import and excel file and create a table from that info. Now I need to confirm that the import has the correct Datatype of Number and Fieldsize of Double for one of the columns.
View RepliesI am writing the following code to check if 'Active' field in table TypeTable2 is ticked or not for records. If I write the following statement to select the records from Access table where Active is not ticked then it gives "data Type mismatch in criteria expression".
Code:
strsql = "SELECT distinct EnvelopeType FROM TypeTable2 where Active='Yes'
I am trying create some code that checks if the contents of the status field in a table is "Authorised" where the ID = something specific for multiple records. If all records witht the specific ID are "Authorised" Then generate a new record in another table. Where to start, perhaps a Dlookup?
View 1 Replies View RelatedI'm a having a problem with a Yes/No datatype. Fundamentally, my code looks like this:
strSQL = "SELECT * FROM tblAddresses " & _
" WHERE YPID = " & Me.Parent.IDNUMBER & " AND CurrentAddress = yes"
Set rs = db.OpenRecordset(strSQL)
Now this statement returns 3 records even though only 1 Current Address exists.
false also returns 3 records
true also returns 3 records
no also returns 3 records
-1 also returns 3 records
0 also returns 3 records
1 RETURNS 0 records!!
If I look at the table in Access, I only have 1 CurrentAddress record for my IDNumber.
Will keeping your field size shorter result in a smaller MDB file?
Or does Access only use as much space as there is real data in its fields.
Way back in the dBASE III days, dBASE would pad all your "real" information with as many spaces as necessary to fill up your field. I suspect that the MDB structure is probably smarter than that.
Another question on the same topic - I believe there is a maximum number of characters in a record (4000?). Can your field sizes add up to more than 4000, as long as the actual data, all combined, never totals 4000...?
Thanks............
..dc
Hi
How I can make the column widths for list box or for sub form automatic (auto size)?
Any help will be appreciated
vbnt
i have multpile yes/no check box i want if i check on one the name of this check box is written on another one.
so i neeed a code for loop of check boxes and i need a code i.e loop all check boxes if any is true then put name field in {new field}
What is wrong with this criteria...
RecSet.FindFirst ("[SomeField] = '" & mMESTO & "'") and ([CheckField] = True)
I have a table that stores ID,PolicyNumber,NewPolNo fields.It has millions of records. The field NewPolNo is empty. I need vba code that will check the following for each record:
1. If the length of the PolicyNumber is over 14 characters long then store that PolicyNumber value in NewPolNo field.
2. If the length of the PolicyNumber is less than 14 characters long thenagain store that PolicyNumber value in NewPolNo field.
3. But if the length of the policyNumber equals to 14 digits long then check the following:
a) If the last 7 digits are 0's of that PolicyNumber. If yes then store first 7 digits in NewPolNo field.
b) If the last 7 digits are not 0's then store the whole PolicyNumber value in NewPolNo field.
We need to check these conditions for each record of the table.
I am storing values of pictures and the location of them in a table, this works fine!... using OpenRecordset. The problem is that when the function is called to store the information, it just keeps adding the same values of each file in the folder over and over again in a word "Duplicating" the information.
I have tried various methods using the OpenRecordset, but cannot seem to find the correct manor of applying the code.
Below is the function I have for storing the data...
Code:
Public Sub GetFilesNamesFromFolder(strFolderPath As String)
On Error GoTo ErrorHandler
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.folder
Dim objFile As Scripting.File
[Code] .....
Our quotation tool, which is built in access 2003, has a memo field, which is extracted in a .txt file, to be uploaded by another system.
When the user starts the memo field with a blank line, by hitting the enter key, the upload file writes the memo field as a new line, separating it from the line indicator and thus giving errors.
My question would be : How would I determine in VBA, whether the first line of the memo field is blank.
I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.
Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant
Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer
Dim I As Integer, J As Integer
ReDim varTempArray(UBound(FieldArray))
intArrayValues = 0
[Code] ....
As you can see, this works to find these values while ignoring NULLS. How to return the field name?
I have a field within a form that needs to be modified based off of someone's access level and I have written that part but not sure how to keep others from modifying the field.
Code:
Private Sub Qty_Rcvd_Click()
If ap_GetUserName() = "Danny_Davis" Or ap_GetUserName() = "christopher_ayers" Or ap_GetUserName() = "Tena_McCrackin" Then
GoTo 10
Else
MsgBox "You are not authorized to view this form"
End If
10
End Sub
Right now this code will bring up the msgbox for someone not authorized but once they hit "ok" the msgbox goes away and they can still change the field. What can I type in here to lock down the field for someone not authorized?
I am using MS Access 2010 and I am struggling to write some vba in my database. On my form, I have a Payment Type dropdown field.When someone selects a payment type I want the code to calculate the end date. Then populate the End Date field with the answer.I will be having a few payment types too, but for now on the dropdown I have two.2/Month and 24/Annum.
View 3 Replies View RelatedI have this linked table query from a OBDC and I need to be able to filter out specific dates in that query. The dates in the table were in text format and I converted the dates using the CDate function. I wanted to filter the query to a single date and always I get the Datatype mismatch in criteria expression error.
However, filtering dates does work only when there are other specifications in the criteria fields (e.g. if I specify a date and and name). My SQL code in error looks like this:
SELECT
purch_hist.PUITM AS ITEM,
purch_hist.PUPO AS PO,
purch_hist.PUQTY AS QTY_RECEIVED,
CDate([purch_hist.PURDT]) AS RECEIPT_DATE,
itmcnt.ITBYR AS BUYER,
purch_hist.PUCST AS UNIT_COST,
vendor.NVNO AS VENDOR NO,
[code]...
This query works fine with a non converted date field, however the dates I need are in text format and need to be converted since I do not have permissions to edit the tables.
The DolphinBatchNo has number data type but the following sql statement doesn't capture the ight records. it doesn't check id dolphinBatchNo is blank.
Code:
strsql="select * from `MasterTBL` where PolicyNumber>=" & TxtFPolNo & " and PolicyNumber<=" & TxtLPolNo & " and PolicyStatus='Live' and DolphinBatchNo is null order by PolicyNumber"
I am designing a project in which there is a form and on that form there is a textbox and Browse button. With browse button you can select a file from the dialog box and that file path will appear in the textbox. This part has been done as below:
Code:
Private Sub CommandButton1_Click()
ChooseFile
End Sub
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] ....
Now the next Part i.e Part2 in which we have to see the references present in Column D of the "Summary" worksheet of the workbook whose path is present in textbox1 and check if they match with any of the references in field "Ref" of the Access table named tblLiterature.
If value in column D matches with Ref field of the Access table then change the status field of the Access table to the corresponding columns A,B,C named as "Withdrawn","Obsolete","Updated". that means if the column A of the corresponding Reference is Y then change the status field to "Withdrawn". If column B is Y then change the status to "Obsolete" and if column C is Y then change the status to "Updated".
Please see attached the workbook as this kind of workbook will be searched against Access table .
convert the following into VBA code for an Access 2007 field on a form. It is needed to create a conditional default value based on another field's category: (Note: TransType, MembershipYear and Dues are field names and are all on the form). Based on the TransType (which is really a category of membership) I want the Dues field to have the applicable default value automatically entered.
For MembershipYear > 2008
If TransType="Individual" or TransType="I" Dues=35.00
If TransType="Family" or TransType="F" Dues=50.00
If TransType="Founder" Dues=100.00
If TransType="Student" Dues=10.00
If TransType="Lifetime" Dues="" or is Null
If (TransType is Null or TransType="") and TotalPaid >0, Dues=35.00
Basically, what's the best practice or how do we store a query's value into an array then checking what the max or min value is and how to check if let's say "4" is in the array?
View 1 Replies View RelatedI am using the function below to calculate a field that excludes weekends and holidays. The weekends are excluded as it is now, but when I try to add in code to exclude holidays I am getting errors. The code for the holidays is in bold and a couple of the errors are Loop without Do so I remove the Loop then I get a Else without If.
Code:
Option Compare Database
Public Function WorkingDays(Due_Date As Date, Result_Date As Date) As Integer
'-- Return the number of WorkingDays between Due_Date and Result_Date
On Error GoTo err_workingDays
[Code] ....
friends,
I have a form based on a table (master) with a cbo which returns yes or no according to what users select.
I then have another form which get's its data from a query based on the above table (master). This time, the value I have selected in the previous form using the cbo, should be displayed using a check box.
This is how I would like it to work:
If Me.cbo = yes (meaning that the value selected is yes), then
check box should show be checked.
If Me.cbo indicated NO, then, the check box should not be checked.
Is there a way I can do this via code?
Thanks.
I'm trying to get a field in my Data Access Page to display the last time a field was updated, I figured I would have to do this in HTML, after a lot of dead ends elsewhere. Here is the code:
<SCRIPT language=vbscript event=BeforeUpdate(dscEventInfo) for=MSODSC>
<!--
Dim DateModified
DateModified = Date 'Current System Date
dscEventInfo.DataPage.Recordset.Fields("DateModified") = Date
-->
</SCRIPT>
Any clues where I would enter this in the HTML source?
I am trying to create form. I have a check box in my form with the name ChkDone.
I am going to write code for this check box and link it with some fields in my form.
The plan is if the check box is true or yes those fields will be available to insert data and if not they will be disabled.
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code:
Private Sub Update_DOD()
Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset
Dim DOD As String
rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code:
CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
Here is my problem. I have several fields located on a form. The user has a problem finding the curser on what field its on. I did ok with the text fields, but I need to come up with something for the check boxes. Any Ideas
The code below works good for text fields. But I need to do the same for Check Boxes or the Check Box Label that is associated with the check Box.
Private Sub Ctl4bCode_Enter()
Me.Ctl4bCode.BackColor = RGB(255, 0, 0)
End Sub
Private Sub Ctl4bCode_Exit(Cancel As Integer)
Me.Ctl4bCode.BackColor = RGB(255, 255, 255)
End Sub
thanks
Hey guys nice and easy one im trying to put some info from a cookie into an access database this is the code i have:
Code:<%dim uiduid=Request.Cookies("tmg_login")("MemberID")sql="INSERT INTO genSiteNews (NewsTitle,News,NewsBy)"sql=sql & " VALUES "sql=sql & "('" & Request.Form("NewsTitle") & "',"sql=sql & "'" & Request.Form("News") & "',"sql=sql & "'" & uid & "')"on error resume nextoconn.Execute sql,recaffectedif err<>0 then Response.Write("Error!")else Response.Write("News Entered Ok!")end ifrs.closeoconn.close%>
comes up with error any ideas?