Filling Null Characters With Zero Character
Dec 6, 2006
Hey ppl. I run a query based on a table and 2 other queries, and based on these I get Fcode field from the table and two number fields from the queries (one from each query). If the number is null in either of the fields (it can be null because the number comes from a count statement) I cannot add the 2 fields together, because I always get null unless I have a number on both of them.
Anyway because I think I've confused you enough the title describes best my needs. How to put zero character within a query to replace Null?
Can this be done?
Thanx in advance
Kyriakos
View Replies
ADVERTISEMENT
Mar 27, 2006
So I went searching through the forums and found a thread that provided coding for getting the # of years and months from two dates:
Function fAgeYM(StartDate As Date, EndDate As Date) As String
'Purpose: Returns the difference between StartDate and Date in full years and months
'To call:
' ? fAgeYM(#1/21/04#, #1/19/06#)
'Returns:
' 1 years 11 months
Dim intHold As Integer
intHold = Int(DateDiff("m", StartDate, EndDate)) + _
(EndDate < DateSerial(Year(EndDate), Month(EndDate), Day(StartDate)))
fAgeYM = Int(intHold / 12) & " years " & intHold Mod 12 & " months "
End Function
------------------------------------------
That's perfect for what I'm wanting but I'm doing it with Hire/Term dates. So I want to be able to see how long past and present employees have worked in the company. Obviously that makes the Term field Null at times. With the coding above it requires a date to be in the EndDate field. How can I change it to allow for a null field in which the field would essentially be the current day's date? [Now()]
View 5 Replies
View Related
Apr 11, 2013
How to match 4 character word or number or combination by identical 4 characters word or number or combination in one word have 10 or 15 characters.
I have to two separate tables (Table A and Table B). Table A has one column (Tag No) and Table B has about 15 columns with one column name Tag No as below
Table A
Table B
Tag No
Tag No
2009
ZZZ-2030-DC
2010
ZZZ-2010-M9P
[code]....
They're both in MS Access.I am trying to match 2 tables - columns (Tag No) with join query, but not success. I want to match 4 characters in Table-A with 4 similar characters in Table-B (Tag No) cell.What query is suitable to compare two tables.
View 8 Replies
View Related
Nov 16, 2014
I do not understand what is happening here. I have foll0wing line in a calculated query field:
m: Switch([EmpID]<5,1) ' run Query 18 in attached example, A2007/2010
this produces 1 for all EmpID<5 and Null for all other EmpID's. All as expected.
But if I do this:
m: Switch([EmpID]<5,1,[EmpID]>=5,Null) ' run Query 19 in attached example
then the entire column is set to Null
View 2 Replies
View Related
Jul 5, 2013
It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:
Code:
Private Sub cmdUpdateDates_Click()
'###################################
'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time].
'###################################
Dim intCounter As Integer
intCounter = 0
Dim rs As ADODB.Recordset
[Code] ....
View 1 Replies
View Related
Apr 18, 2006
Hello all,
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
Thanks.
View 4 Replies
View Related
May 23, 2007
Hi all:
I have an inventory table, and I wish to pick items from that inventory table and populate an order placement table.
In oracle, clicking one of the item windows in the form provide with a LOV (List of Values), and usually items are picked from there.
How do I achieve this in Access, and it does not have to be LOV as in Oracle.
I shall be thankful for your kind help.
Regards - Prabir.
View 4 Replies
View Related
Nov 10, 2005
I have a table with names of people and the cars they own. I made a form with a combobox which everytime i choose the name of the person in my combobox, it will list the cars they owned. That works fine. Problem i have is that i have to show how many cars they own. So i made a query to count the number of cars for each person. Then I made a textbox on the form to show this count. However I can't seem to link this textbox to the query since my form has a record source to the table of ppl and cars. How do i link my textbox to my query of counts of cars?
I tried changing the control source pointing to the field of the query but it doesn't work. I have linked the combobox selection so that my query filters to wat i select on the form. Anyone can help :( , its a simple process but i keep trying and doesn't work, and also dont wanna use a subform.
View 1 Replies
View Related
Dec 5, 2005
hi
is there anyway to temporarily store the information being entered into a form before actually writing it to a table. i want information to be added to the table only once the save button is pressed. i dont want the table to be used even as the scratch pad.
Thanks
View 3 Replies
View Related
Jul 27, 2006
Hi all,
I have been trying to create a database that looks after staff holidays.
Here's how it looks at present
TBL_Holidays:
Staff_Index
Staff_Name (From a lookup)
Start_Date
Number_of_days
TBL_Holidays_Booked:
Staff_Name
Dates_Booked
Once the form (FRM_Holidays) is filled out I had (until I gave up and decided to ask for help - 2 weeks) a button that went off and run a append query.
Here's my problem. Once the button is pressed I need the TBL_Holidays_Booked to fill with the Dates_Booked.
ie.
John Smith wants his holiday to start on the 1st August for 10 days.
The resulting TBL_Holidays_Booked would look like the following:
John Smith 01/08/06
John Smith 02/08/06
John Smith 03/08/06
..
John Smith 10/08/06
Is there a way to do this? or is there a simplier way of doing it?
Regards
Mark
View 3 Replies
View Related
Feb 11, 2005
I have a form, which is used to create proposals. The form has form headers and footers, as well as a page footer.
In the Detail area of the form there are only two fields, Description and Amount, which only take up one line. I want the detail area to determine how many pages the form will be when printed. Can a new description/amount fields line be automatically inserted after I complete one? I guess the question is, can the Detail area of the form grow automatically? If so, how is it done.
Naturally, I only want the Form Header and Footer to be printed once, regardless of the amount of pages the proposal becomes. And, likewise, I want the page footer to be printed on each page. So, if the detail area only has one line completed then only one page will be printed. However, if there are 20 lines in the detail area then two pages or more will be printed.
Any help would be greatly appreciated.
MrAviator
View 1 Replies
View Related
May 30, 2005
I have a table:
Product:
Code | Name | Description | Price
I have a form with a text box. i want whatever i write in the text box to be inserted in the table Product under Code.
Its like im inserting a new product.
How can i do that? Im new to Access and VB, so bare with me.
Thanks alot
View 1 Replies
View Related
Nov 14, 2007
Hello!
What I am trying to do is fill a field with a date until a new date is encountered. Basically, I have imported some csv data which only has the date when it changes. I have created a new field and have a row with only the date in it. Is there a way to fill the rows below the date row with that date until the next date row is found and then continue on with the next date and so on. Here's what the database looks like, basically:
Date Field1 Field2 Field3
01/01/01 null null null
null DATA DATA DATA
null DATA DATA DATA
null DATA DATA DATA
null DATA DATA DATA
01/02/01 null null null
Hopefully this makes sense and someone can give me a hand with this! Thanks so much!!
View 14 Replies
View Related
Dec 21, 2004
I have one table (tblComplete) with a listing of 30-50 company names and associated information...
"tblComplete" has fields: AutoNumber, Company, Address, City, State, Zip, Phone
On a form I created a combo box linked to AutoNumber and Company to drop down the list. Once the 'company' is selected, I would like it and the rest of the fields (address, city, state, zip, and phone) to go into the blank table (tblSelected).
Could I use a Macro to do this or should it be a query?
View 9 Replies
View Related
Nov 11, 2005
I need to auto populate a sub form.
Main Form
ID
Top Assembly Part Number
Top Assembly Serial Number
Sub Form (continuous form)
ID
Part Number
Part Name
Part Serial Number
Main Form and Sub Form linked by ID
(There is a separate table that has sub form part numbers for each main form part number)
Based upon the part number in Main Form I need to automatically populate the sub form with Part Number and Part Name.
Some Main Form Part Numbers have 85 sub form part numbers others as few as 3 sub form part numbers
Main Form
______________________________________
|
| ID [123 ] |
| |
| Part Number: |9999999 |
| Serial Number: |ABC-9999 |
Sub Form
_____________________________________
|
| ID [123 ]
||Part Number | Part Name | Serial Number |
|| 1111 | Widget 1 | A11111 |
|| 2222 | Widget 2 | B1234 |
|_____________________________________
View 1 Replies
View Related
Feb 16, 2005
Does anyone have a quick sample code on how to load a combo box using ADO? Currently I set the row source type to Table/Query and the row source to a query within the program. I would like to fill my combo box using code instead.
View 5 Replies
View Related
Feb 18, 2005
Is it possible to write an event procedure attached to a command button in a Microsoft Access database that would use data from an established query to pre-fill data in to a form on a web site? Has anyone tried this?
View 3 Replies
View Related
Jun 9, 2005
I have a form that the user will fill, then email it to a customer.
I dont want to send the whole table, just the values that the user just inserted in the form.
Any idea on how should i do that using Access?
I am aware of the SendObject method, but i never used it, and dont know how. Any help will be very apreciated.
Thank you in advance.
View 2 Replies
View Related
Jan 25, 2006
This is probably very simple to do, but, I am most likely missing the obvious...
I have 3 fields,
UnitPrice,
NumberPerUnit, and
NumberPerUnitCost.
I would like to have the NumberPerUnitCost field filled automatically based on what I have entered into the other 2 fields. I would also like to have this saved in my table as I will use this single unit cost when calculating some production costs.
Any and all help would be appreciated. If I am going about this the wrong way, please redirect me.
Thanks, Karen
View 3 Replies
View Related
Feb 2, 2006
Hi eveybody,
I have 10 fields in a form where I put in years.
is it possible that if the first year is filled in as 2006 (for
example) that the next nine years fill up automatically as 2007, 2008, ......etc.
How would I do it?
Thanks
dfuas
View 7 Replies
View Related
Feb 9, 2007
Hello
I have one question
I have simple table in Access 2003 with three fields
One is (field name), type - autonumber
one is (field name), type - number
one is (field name), type - text
my problem is that i need in number field nubers between1 and 2000 and in text fields same numbers between 1 and 2000 with letter G at the end.
that will look like
Autonumber 1 ... 574 ... 2000
Number 1 ... 574 ... 2000
Text 1G ... 574G ... 2000G
Problem is that I dont know how to write a code for that i think that there would be a macro.
So can someone help me please!
View 7 Replies
View Related
Feb 26, 2005
I have a basic design question that I am not sure how to address.
I am trying to build a simple data entry database with a form to take input from the user, store the values in a table. Once the data is gathered into the table I want to use this table to print a report of each record (entered using the form before).
To achieve this objective, I made a form (frminput) with some text fields. Most of the fields on this form are Bound fields to a query (qrymaster). One of the field is a Combo box (whose value is shown from another table). I have designed the RecordSource of this Form to be a Query (qrymaster).
This is a basic Data Entry form where the user selects the Combo box item and based on what he selects, some of the fields in this Form gets pre-filled. The rest of the fields on this form are bound to the query "qrymaster" and the user has to type these fields manually.
Now, I want a Save button here that would save all the values on this form to the table "tblmaster". How do I do this efficiently keeping in mind all the normalization laws on the database?
At this time, behind the Save Button, I have included a SQL statement to insert all the field values into the table "tblmaster".
I am sure there is a better way to do this. Can someone point me in the right direction please?
Thanks.
View 3 Replies
View Related
Mar 3, 2006
Dear All!
I'd like to ask you to help me in the following. The issue is basic I think, but not for me...
I'd like to transfer the data entered in the fields of a form into the fields of a table (that uses the same values). This table is connected with one to one relation to the table which the form bases on.
(An automatic data-transfer would be desirable. If it is difficult a button will do as well.)
Please write if you have got any good idea or solution.
Thanks in advance.
BR
xxyy
View 2 Replies
View Related
May 8, 2006
This is simple Access but I am quite simple so help would be cool
all I want to know is how to put text from one field into another, but not every time.
lets explain
I have an order form and two fields , one date booked , and one date requested.
if date booked is empty I want to copy the info from date requested to it, if its full I want Access to say its already booked.
its porbably something along the lines of where datebooked.txt = "nothing" then datebooked.txt = date requested.txt or something like that anyway
View 1 Replies
View Related
May 8, 2006
I have a combo box for rooms: single, twin, double, suite, grand suite
This is found from a lookup table
In the next field on the lookup table is the cost per night for the room, how can i get the cost per night to automatically appear in a text box below the room type combo box.
I asked this the other evening and somebody said it was to do with making the table the bound source or something but i didn't understand.
An explanation would be great
Thanks
View 1 Replies
View Related
Jun 1, 2006
What i'm trying to do is fill several unbound text boxes on a form.
I have an "Employeetbl" with "Employeename" and a "Projecttbl" with "Projectname". what i want to do is have about 10 text boxes from left to right on my form. Then fill these with all the employee names from my "Employeetbl".
Underneath this i will have about 10 text boxes from top to bottom and i'd like to fill these with the project name from the "Projecttbl"
anyone have any idea?
Matt
View 3 Replies
View Related
